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

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

  • 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!

  • 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.

  • 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

  • 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,

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

  • 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.

  • 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.

  • 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

  • A C#.NET toolkit for writing SSIS Script Tasks

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

  • Super, thanks, Stan

  • Thanks, Stan. This looks very helpful.

  • 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.

  • 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!!!

  • @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)

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

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