June 15, 2011 at 6:49 am
All, I need your help in creating one SSIS package. Below is the Detail :
1.Copy the data from source to a destination table base of some condition (Straight Forward)
2.Move the files from Source location to Target Location ( this is the tricky part)
•The Source Path is in given in the Table.
•Each file which needs to be move has a FILE_ID and one FILE_ID may have more than one file assosiated with it.
•Create a new folder on the destination with the FILE_ID number and Date Time.
•If one File_Id have more than one file, all the files should go the same folder.
3.Update the destination table Stats Columns after moving the files..
Please let me know that what is the best approch of doing this.
Thanks in advance..Ar
June 15, 2011 at 8:55 am
I would probably use a script component for step 2, if you have more than one file I would probably output the list form a query or sproc into a variable in SSIS with the type of Object. Then specify that object as a readonly variable in the script, I would then iterate through that record set which I BELIEVE is an ADO recordset. you could then use simple .net commands to move the files.
CEWII
June 16, 2011 at 5:18 am
Why use a Script Task when a File System Task might work just as well? You can set variables at the package level and have them populated by the names in the table either by a For Each Loop (depending on how the files are set up in the file directory) or an Execute SQL Task.
June 16, 2011 at 7:57 am
Brandie Tarvin (6/16/2011)
Why use a Script Task when a File System Task might work just as well? You can set variables at the package level and have them populated by the names in the table either by a For Each Loop (depending on how the files are set up in the file directory) or an Execute SQL Task.
For an Execute SQL Task you basically have to use xp_cmdshell, which I generally don't allow, so that one is out. And I don't like using the File System Task, I have had trouble with it in the past and with just a couple lines of code(<5) I can move/copy files with absolutely no trouble. You are free to take your own path but that is mine.
CEWII
June 16, 2011 at 8:09 am
Elliott Whitlow (6/16/2011)
Brandie Tarvin (6/16/2011)
Why use a Script Task when a File System Task might work just as well? You can set variables at the package level and have them populated by the names in the table either by a For Each Loop (depending on how the files are set up in the file directory) or an Execute SQL Task.For an Execute SQL Task you basically have to use xp_cmdshell, which I generally don't allow, so that one is out. And I don't like using the File System Task, I have had trouble with it in the past and with just a couple lines of code(<5) I can move/copy files with absolutely no trouble. You are free to take your own path but that is mine.
And if the path names are stored in a SQL Server table, like the OP said, what's the need for xp_cmdshell?
June 16, 2011 at 8:53 am
To perform the moves of the files.. Did I miss something (entirely possible 🙂 )..
CEWII
June 16, 2011 at 9:07 am
Elliott Whitlow (6/16/2011)
To perform the moves of the files.. Did I miss something (entirely possible 🙂 )..
That's why I advised a combination of Execute SQL (to load the variables) and the File System Task.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply