Post reply

ForEach Loop With Different File Extensions

  • November 4, 2010 at 12:05 pm

    #226044

    Hello all,

    I am having a problem with the SQL Server SSIS ForEach Loop Container. I am running 2008, but I don't know if it is limited to that version only.

    I have a bunch of Excel files, some 2003 with .xls extensions and some 2007 with .xlsx extensions. Each of them have to be treated differently. I have put two ForEach Loops in my script, one with the file filter "*EOL*.xls" and the other with the file filter "*EOL*.xlsx"

    My issue: The xlsx one will only loop on the 2007 files - GOOD JOB! But the .xls one will loop on both 2003 and 2007 files. How can I make the .xls one only pick the .xls files???

    Thanks ahead for any help!

    Shalom!,

    Michael Lee

  • November 4, 2010 at 12:47 pm

    #1246767

    I'm sure someone else has better advice, but one idea would be:

    Do a check on the variable that gets populated by the filename each time the loop runs. Check to see if it ends with an "x" string or not. If it ends with an "x" then throw it out, otherwise process it.

    You might be able to do this:

    http://www.bidn.com/blogs/MikeDavis/ssis/559/ssis-skip-certain-files-in-a-for-each-loop

    Or you might end up needing a script task:

    http://timmitchell.net/post/2010/08/23/ssis-conditional-file-processing-in-a-foreach-loop.aspx

  • November 4, 2010 at 2:19 pm

    #1246854

    Thanks! I was trying to be lazy and use the tools provided by SQL Server, oh well...

    I rewrote the job using a script to decide which type of file it is, then set the Presentence to route it to the correct data flow task.

    Thanks for your help!

    Shalom!,

    Michael Lee

Viewing 3 posts - 1 through 2 (of 2 total)

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