Hi,
I'm tring to copy files from FTP address, the problem is that sometimes the FTP folder is empty, and then the FTP Task is failed.
Why is it failed if there are no files? Any suggestion how to avoid the error?
Thanks,
Hadar
In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.
I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.
Thanks for the help in advance and i apologize for the long lines of code!
example for step 1:
----------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class
example for step 2:
-------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "ftp.name.com")
cm.Properties("ServerUserName").SetValue(cm, "username")
cm.Properties("ServerPassword").SetValue(cm, "password")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
ftp.Connect()
'ftp.SetWorkingDirectory("..")
ftp.SetWorkingDirectory("directoryname")
Dim folderNames() As String
Dim fileNames() As String
ftp.GetListing(folderNames, fileNames)
Dim maxname As String = ""
For Each filename As String In fileNames
' whatever operation you need to do to find the correct file...
Next
Dim files(0) As String
files(0) = maxname
ftp.ReceiveFiles(files, "C: emp", True, True)
' Close the ftp connection
ftp.Close()
'Set the filename you retreive for use in data flow
Dts.Variables.Item("FILENAME").Value = maxname
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
I'm using the FTP Task in SSIS to send files. They task succeeds but the files get uploaded to the wrong folder. Instead of being sent to the cg268301 folder they are being sent to the cg268300 despite selecting /cg268301 from the remote path field in the FTP task editor.
I've tried uploading this file to the /cg268301 file using an execute process task and it works fine. I just don't know why it won't work with the FTP task.
Hi,
say I have th following in my post-deployment script:
:r ..ScriptsFolderScript1.sql
:r ..ScriptsFolderScript2.sql
:r ..ScriptsFolderScript3.sql
...
How can I do the equivalent of
:r ..ScriptsFolder*.sql
??
I've tried the above and the syntax is not supported.
Your help is much appreciated! =)
Hi All,I have a multiple files, but they are store in different directory onthe server. I want open those files and insert it into the databaseusing bcp.Example files structure dir:\xyz123abc ext1.txt\xyz123abc ext2.txt\zyz123999 ext2.txtbcp "dabase" in \xyz123abc ext1.txt -c -S"servername' -Usa-Ppassword -T".is there away to loop througth each dir, get the files, excecute thebcp, then go to next folder.Please help. Thanks in advance.Ted Lee
View 2 Replies View Relatedto utf8 format through BCP or throuhg the dos command prompt?
Cheers
Hello
I'm just starting with SQL and BIDS. I have created a xml-Import to SQL Server which works without any problem.
Now I like to import (daily) all xml-files of an appropriate Folder at once to the DB. The files will have different names each day.
I could not find any help in the internet to solve this problem.
Can someone give me an Idea how to import many xml-files out of the same folder to SQL?
Thanks & regards
Chaepp
Convert 100 xml files individually to pdf's and zip them in a folder along with the source files.
Can it be possible in SQL server BI world?
If possible make this an automated process for every 100 files.
Hello everyone - wonder if you can help.
How do you process all files in an FTP directory - similar to the for each loop for files.
I need to be able to download each file and then move it to an archive folder on the ftp site.
Might also want to do some things in between.
I have a feeling it means getting the directory listing into a recordset or variable then enumerating that.
Sounds like a common requirement and would be quite easy by other means. Can't help feeling that SSIS has built in tasks to do it.
can sql server 2005 access files on a shred folder (which sql 2000 was not able)?
thnaks in advance
peleg
Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
I am developing application with SQLCE2.0, NETCF1.0,Sp1,VS2003.
I found there is some files are created under "Temp" folder by the system with size "0B".
Why/when these file are created? Do I need to clean it periodicly? If not, will this cause exception like "Not enough storage is available to complete this operation"?
Thanks.
Hello;
I started to get out of space on the server C: drive.
I checked and I was using file system storage on the C drive in:
C:Program FilesMicrosoft SQL ServerMSSQL.xReporting ServicessRSTempFiles
I checked the folder and it is 1 GB.
My questions or doubts;
1) I read that the path can be changed without any problem, in the same server we have a D drive with 40GB of space, so I am planning to change the path to: D:RSTempFiles. Any experience on changing that path?
2) If the change of path is successfull, how I can delete the files on the RSTempFiles on the C: drive. Can I directly delete the files? Ther just previous snapshots or I will lose something? Any experience with this will be great.
Thanks everybody and nice weekend.
Luis
I have installed SSAS on a 64 bit machine and am trying to connect to Oracle but having little success.
Some of the solutions I've read indicate to uninstall SSAS and then reinstall, making sure that the reinstall does not use the Program Files (x86) folder since Oracle has a problem with the parentheses.
Here's my question... How do you NOT install in the Program Files (x86) folder. It seems SQL Server always wants to put a small part in there.
Thanks in advance and sorry if this is a dumb question.
I'm wondering if its possible to return the number of files in a folder suing something like DIR command.I'm wanting to do something like, If count(DIR) >0 then do something
else End
exec master.dbo.xp_cmdshell
'dir C:Test'
Hi All,
I have a challenge i am trying to overcome, hopefully soneone would have come across this issue before..
I am creating a DTS package that will be scheduled to run at a certain time everyday. A source folder exists that get a set of new files everyday.The DTS Package will then read each file and copy the data into a load table in my database the challenge is this:
I am trying to load files from a source folder into my load table, Within each file, the entires are in a specific format using pipes to seperate the data that goes into which column e.g
example of a file entry:
column1 | column2 | column3
data1 | data2 | data3
data1 | data2 | data3
data1 | data2 | data3
And now i am using DTS to specify the file format and map the cloumns as apprporiate to my table...all this is well and good, but my problem is each file has a different name as well as being timestamped, now how do i use DTS to specify the source folder, open each file sequentially and read (or more appropriate, copy the entries into my table, inserting new data from each file into my load table as well as overwriting old data in the load table from the files in the folder ?) is there a way in specifying your source folder in DTS rather than specifying the file in the Menu options (in the transformation data task properties )given, and or do i need to write a script for this(reading the file?)
can someone please give me a solution and how to approach this?
thanks in advance
Hi, I am wondering how can we return each file in a folder ? I am trying to get each file in a folder to perform other job. Below is the description ...
while folder is not empty
foreach file
run the job
end for
end while
Hi,
How do I execute all the .sql scripts in a folder with OSQL command?
Thanks in advance,
Hari Haran Arulmozhi
SET nocount ON
DECLARE @fileNameNew VARCHAR(1024) -- filename of new error report
DECLARE @fileNameOld VARCHAR(1024) -- filename of old error report
DECLARE @out_fileDate CHAR(1024)
DECLARE @fileDate DATETIME -- used for file name
[code].....
We noticed SQL Server 2005 is creating Program FilesCommon FilesMicrosoft SharedDW on our largest drive for each 64-bit installation. Does anyone know what this is? It appears there is no Microsoft documentation regarding this installation and if we need to keep it. It may be .NET related, but I have no idea why it is needed.
Dave
Is there a way to send an e-mail from SSIS that attaches all the files in a specified folder?
View 5 Replies View Related
I am looking for a way to truncate raw files without losing the metadata. The metadata of the raw file should be automatically detected at run-time. The result will be a raw file with the same metadata as the original file, but no data in it.
There are two reasons I would like such a tool. First, I want to erase the potentially sensitive data stored in the raw files. Second, I want to keep Validation enabled so that development is simpler.
Is anyone aware of such a tool?
Thanks,
Using the below script task I am checking for the excel file existence and upon file existence using the data flow task will load the excel data into sql table. After the data is loaded from one file or however number of excel files present, I want to move those into a archieve folder with datetimestamp to the filenames,please let me know how I can move those files with datetimestamp to the filenames, any help is greatly appreciated. Thanks!!
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Public Sub Main()
If File.Exists(ReadVariable("FileNameVariable").ToString()) Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
'From Daniel Read's Blog - http://www.developerdotstar.com/community/node/512/
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class
Hi,
My Problem goes Like this....
I have a folder which contains all the flat files which are used by all the packages(ex--flat file connection managers) in my project.
If we want to change the name of the folder,have to change in every package( in all connection managers) manually.It looks hardcoding and timetaking.
Is there any way to change in one place(xml,file,variable) so that it should be affected in all the packages.
one more doubt is..
If we configure the flat file connection manager in package configurations,configuration file (ex-xml)will be created (we can make changes in that file regarding that connecion mgr only.)
But i want one configuration file (ex--xml) so that i should configure the details of all the connection managers used in all packages.
Using the below script task I am checking for the excel file existence and upon file existence
using the data flow task will load the excel data into sql table. After the data is loaded from one file or
however number of excel files present, I want to move those excel files into a archieve folder with date×tamp to the filenames, please let me know how I can move those files with datetimestamp to the filename,
any help is greatly appreciated. Thanks!!
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Public Sub Main()
If File.Exists(ReadVariable("FileNameVariable").ToString()) Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
'From Daniel Read's Blog - http://www.developerdotstar.com/community/node/512/
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class
I have a small project to be done in which I need to fetch the pdf file from a my system and save it in database and also fetch the name of it and save it in the database.
View 9 Replies View RelatedMy files are stored as Newborn.txt under each year's subfolder, How to implement this.
Eg: C:RaviN90Newborn.txt
C:RaviN91Newborn.txt
C:RaviN92Newborn.txt
C:RaviN93Newborn.txt
How to read the files using the for each loop container"
I'm having an issue where a folder that I'm accessing in an SSIS package gets locked up and cannot be deleted by my package later in the process.
1) I use a File System task to rename the folder. At this point, it appears to become locked and cannot be deleted.
2) Later I attempt to delete the folder using another File System task object. I get the error '[File System Task] Error: An error occurred with the following error message: "The process cannot access the file because it is being used by another process."'.
My package accesses the folder via a drive mapping. This seems to be part of the problem, as it's possible to delete the folder directly but not via the drive mapping. Even once the package completes it's still not possible to delete the folder in Windows Explorer via the drive mapping.
I wrote the below script to print all folders and files located in the share path. How to extend my script to mention by adding another column whether the file is a folder/file , sort of 0 or 1.
declare @chkdirectory1 varchar(4000) = 'shared_pathfolder';
declare @finalserver3 varchar(4000);
create table #tmp (directory_name varchar(4000))
SET @finalserver3 = '''"DIR ' + @chkdirectory1 + ' /B"''';
--select @finalserver3
--SELECT @finalServer
DECLARE @ExecCmd varchar(100)
--SELECT @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + char(50) + 'mkdir D:'+ CONVERT(varchar(8), getdate(), 112) + '' + char(50)
SET @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + @finalserver3
--SELECT @ExecCmd
exec(@ExecCmd)
drop table #tmp
I've been struggling with this issue,
1) Test--FolderName (This Test folder name should use as a database name for below sub folders)
a)Create--Sub Foldername
i)create.sql
b)Alter---Sub FolderName
i)Alter.sql
c)Insert---Sub FolderName
i)Insert.sql
[Code] .....
The scripts need to be run in order. So script one needs to run first folder in that sub folders after that next second folders etc..
Is there a way to create a bat file that automatically runs all these scripts, in order against, the databases they need to?
The databases that they need to run against have the name of the database at the beginning of the name of the folder.
I have set up an FTP connection that tests successfully. I can log on to the FTP site with the same credentials and see my root folder, and within that, two more folders. In my FTP task, I want to receive files to my local machine. The problem is that the only remote path available is not at the root level, and the only thing I can see are files from one of the child folders, but not the child folder I want.
Is it possible in the remote path to change folders? The remote path just shows a "/", which I thought would be the root level, but it is somehow linked to a child folder.I've tried various combinations to get to the folder I want, /root folder/child folder, but that gives the error that the folder does not exist.
If I open the FTP Task Editor, click on File Transfer, and click the ellipses for the Remote Path, the Browse For File box opens, with a "/" in the Location section, and a list of files in the child folder that I do not want to be in. If I click the "Up Directory" button, I get the message "Already at top level directory".
how I can get the files from one particular folder on the FTP site?
how do I copy a folder from an FTP location using the FTP task in SSIS. Currently, I can only move the files in the folder one after the other but I want to copy the folder at once.
View 3 Replies View RelatedI have 2 source folder .I have created variable for both source folder like below
User::Source1 and User::Source2
and 1 destination folder variable like below
User::Destination
Now I have to search a file from both source folder which consist of *location_*.xml string in file name.I have to use C# Script task ,achieve above scenario as I required it on very urgent basis.
I have created for each container to call all the packages in a folder like below, also created a variable.
Then I add execute package task inside of foreach container and selected file system in a location and in connection called currently creating package name finally in connection properties i added variable in expression which i created and mapped into for each loop container. I referred below link
[URL] ....
All the packages are running but its not ending once all the packages executed its re run and continue the running process, how to stop once all the packages execute.