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
Author
Message
Posted Thursday, June 13, 2013 12:13 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 8:51 AM
Points: 128, Visits: 911
Comments posted to this topic are about the item Batch ETL of Multiple Data Files Using an SSIS Foreach Loop Container
Post #1462908
Posted Thursday, June 13, 2013 2:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:43 AM
Points: 21, Visits: 237
Great guide for anyone looking to get started with file processing in SSIS! You can actually simplify it even more if you like, by using a File System Task, instead of a Script Task to move your processed files once done. That way you don't even need to write any code!
Post #1462942
Posted Thursday, June 13, 2013 6:45 AM
SSC Eights!

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

Group: General Forum Members
Last Login: Today @ 2:43 PM
Points: 814, Visits: 2,014
You can actually simplify it even more if you like, by using a File System Task, instead of a Script Task to move your processed files once done. That way you don't even need to write any code!


Having just spent the past several days developing imports for multiple csv and xls files, I would recommend that a script task would be best to do the move. The move file task seems to be buggy, at least with variables, and the suggested work around of using a copy followed by a delete affects file properties such as creation date.



Post #1463047
Posted Thursday, June 13, 2013 6:59 AM
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
RonKyle (6/13/2013)
You can actually simplify it even more if you like, by using a File System Task, instead of a Script Task to move your processed files once done. That way you don't even need to write any code!


Having just spent the past several days developing imports for multiple csv and xls files, I would recommend that a script task would be best to do the move. The move file task seems to be buggy, at least with variables, and the suggested work around of using a copy followed by a delete affects file properties such as creation date.


I have used the File task with variable and never encountered a problem so I'm curious to know what issues have you had and in what scenario(s). Do you have some examples you could share?

Thanks in advance
Post #1463059
Posted Thursday, June 13, 2013 7:08 AM
SSC Eights!

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

Group: General Forum Members
Last Login: Today @ 2:43 PM
Points: 814, Visits: 2,014
I can only tell you that it didn't work, and searching the internet made it clear that I wasn't alone. As I don't document processes that don't work, only those that do, I don't have a handy example. As a former programmer, I don't find coding difficult anyway. I would be interested in seeing a screen shot of one of your moves where you set the source and destination, though, so I could try to mimic your solution.

THanks,



Post #1463065
Posted Thursday, June 13, 2013 7:23 AM
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
Sure, I'll put something together and post it. Thanks for responding.
Post #1463081
Posted Thursday, June 13, 2013 9:53 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:27 PM
Points: 629, Visits: 139
imz (6/13/2013)
RonKyle (6/13/2013)
You can actually simplify it even more if you like, by using a File System Task, instead of a Script Task to move your processed files once done. That way you don't even need to write any code!


Having just spent the past several days developing imports for multiple csv and xls files, I would recommend that a script task would be best to do the move. The move file task seems to be buggy, at least with variables, and the suggested work around of using a copy followed by a delete affects file properties such as creation date.


I have used the File task with variable and never encountered a problem so I'm curious to know what issues have you had and in what scenario(s). Do you have some examples you could share?

Thanks in advance


Nice post for the people who are looking for simple ways to import multiple files and then moving processed file(s) to backup destination. Even though I have used File System Task to move processed file and never had an issue (lets cross our fingers), I prefer Script Task instead because you can do so much with Script Task.

The only thing I would suggest here is, instead of hard-coding source and destination inside Script Task, I would declare them as variable and pass those variables to Script Task, that way if you need to change source or destination later in future, you can only change the value to the variable without requiring to go into the Script Task. It is good for those people who does not know much of programming. Also, it is better if you need to build an expression out of those variables.
Post #1463174
Posted Thursday, June 13, 2013 10:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 6, 2014 2:13 AM
Points: 1, Visits: 16
This article is good to begin with. I used FileSystemTask to move files to backup folder(without any issues so far), the advantage with this method is the developer need not to worry about VB scripting.
Post #1463200
Posted Thursday, June 13, 2013 11:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:37 AM
Points: 30, Visits: 39
I'm learning C# and SQL. I'm having trouble figuring out the C# equivalent to the VB code in the Script Task Editor. Would appreciate any help. Have tried finding similar code in C# with no luck yet.

Thanks,
Wendell
Post #1463216
Posted Thursday, June 13, 2013 11:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 8:51 AM
Points: 128, Visits: 911
A C#.NET toolkit for writing SSIS Script Tasks

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/76439/
Post #1463217
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse