Batch ETL of Multiple Data Files Using an SSIS Foreach Loop Container

Question

Batch ETL of Multiple Data Files Using an SSIS Foreach Loop Container

Stan Kulp-439977

SSChampion

Points: 10062
More actions
June 13, 2013 at 12:13 am

#279110

Comments posted to this topic are about the item Batch ETL of Multiple Data Files Using an SSIS Foreach Loop Container

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

GShenanigan SSC Veteran Points: 213 More actions · Answer 1

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!

RonKyle SSC-Dedicated Points: 31557 More actions · Answer 2

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.

iMtz SSC Veteran Points: 248 More actions · Answer 3

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

RonKyle SSC-Dedicated Points: 31557 More actions · Answer 4

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,

iMtz SSC Veteran Points: 248 More actions · Answer 5

Sure, I'll put something together and post it. Thanks for responding.

sbamaty SSC Eights! Points: 936 More actions · Answer 6

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.

smart762000 Valued Member Points: 59 More actions · Answer 7

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.

wholmes 69850 SSC Enthusiast Points: 156 More actions · Answer 8

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

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 9

A C#.NET toolkit for writing SSIS Script Tasks

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/76439/

wholmes 69850 SSC Enthusiast Points: 156 More actions · Answer 10

wholmes 69850

SSC Enthusiast

Points: 156

June 13, 2013 at 11:25 am

#1623829

Super, thanks, Stan

wholmes 69850 SSC Enthusiast Points: 156 More actions · Answer 11

wholmes 69850

SSC Enthusiast

Points: 156

June 13, 2013 at 1:51 pm

#1623885

Thanks, Stan. This looks very helpful.

Gerard Silveira Ten Centuries Points: 1106 More actions · Answer 12

Thank you for this post, it helped me understand how the for each loop container writes into a variable , which is then subsequently used in the connection manager.

xxing29 SSC Enthusiast Points: 111 More actions · Answer 13

Welcome to http://www.likesurprise.com//

where is the most popular Panthers online shop.

((( http://www.likesurprise.com/ )))

lower price fast shippment with higher quality!!

WE ACCEPT CREDIT CARD /WESTERN UNION PAYMENT

YOU MUST NOT MISS IT!!!

RonKyle SSC-Dedicated Points: 31557 More actions · Answer 14

@imz--I tried the task once again as I'm working on a project where I need to do that. Although the same variables that are used in the code are also used in the File Task, the error is part of path not found. This code executes without error:

Public Sub Main()

Try

'Move File

File.Move(Dts.Variables("FullyQualifiedUnprocessedFileName").Value, Dts.Variables("FullyQualifiedProcessedFileName").Value)

Dts.TaskResult = ScriptResults.Success

Catch e As Exception

Dts.TaskResult = ScriptResults.Failure

End Try

End Sub

So I will stick with what works. The overwrite version of this would begin with:

If File.Exists(Dts.Variables("FullyQualifiedProcessedFileName").Value) Then File.Delete(Dts.Variables("FullyQualifiedProcessedFileName").Value)