Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««123

Batch ETL of Multiple Data Files Using an SSIS Foreach Loop Container Expand / Collapse
Posted Monday, June 17, 2013 12:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 27, 2014 11:51 AM
Points: 30, Visits: 61
The "Move File" operation will not allow you to change the name so the destination can only contain a path to the file folder without the file name. If you want to move it and give it a different name at the same time then you need to use the "Rename File" operation. It was confusing to me at first and I still don't understand why they chose to give this functionality to the "Rename File" operation instead of giving it to the "Move File" one or even create another called something like "Move and Rename File".
Post #1464308
Posted Monday, June 17, 2013 2:44 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 2:54 PM
Points: 820, Visits: 2,135
Doing it as a rename worked. I'm all for using code, but if a non-code solution will do, I'll use that instead. Thanks,

Post #1464383
Posted Thursday, June 20, 2013 12:39 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 1:12 PM
Points: 246, Visits: 315
I've used both Script Tasks and File System Components to perform file manipulation but try to use native SSIS components to perform an operation when available meaning I tend towards the File System Task unless there's specific reason not to.

Because the Script tasks are basically just wrappers for C#/VB .net code you could effectively write a whole application to perform any operation all the way down to a full GUI if desired, but that's not really what it was intended for. The script task is very powerful but it obfuscates the tasks that it performs by removing the logic from SSIS and placing it in a different environment. It's more a personal preference than anything but I just like being able to follow the logic in a package without having to open up a script task, open the script in a separate Visual Studio code window, look at which DLLs were loaded to perform specialized operations, try to figure out which variables are passed back and forth etc.

Regarding the loading of multiple files of the same format one thing that has been very handy in certain circumstances is the Multiple Flat File connection manager. You can use the same kind of wildcard matching that you'd use in a for each file loop and treat any number of like-formatted txt files as a single file. It's especially handy if your data flows include lookups or other operations that have to occur every time the data flow is loaded since they will only occur once as opposed to every iteration of the outside loop. Not a solution in all cases since you wouldn't be able to do file-by-file archiving as easily but useful nonetheless.

Post #1465896
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse