locked
Foreach loop for flat file output RRS feed

  • Question

  • Hello,

    My SSIS package is working for stored procedure to flat file output. Next, I am going to do this for (5) other files that need to be processed at the same time. So, is best practice to process all of these in one foreach loop container or is it better to process each file in its own foreach loop container and nest those loop containers within one big container? I'm not sure what to do on this but want to make the best tactical decision. I've been sourcing this but not finding a definitive solution.

    Any help greatly appreciated.

    Thanks,

    Buster


    BusterCoder

    Saturday, May 9, 2015 7:47 PM

Answers

  • Hi Buster,

    If you want to achieve parallel processing, then having the files processed outside the parent loop is desirable.

    Moreover, have 5 Data Flow Tasks that are not connected to run in parallel.

    I do not see how you need loops if you call several different stored ptocs, and thus the resulting files are all expected to be different.


    Arthur

    MyBlog


    Twitter

    Sunday, May 10, 2015 1:47 AM

All replies

  • Hi Buster,

    If you want to achieve parallel processing, then having the files processed outside the parent loop is desirable.

    Moreover, have 5 Data Flow Tasks that are not connected to run in parallel.

    I do not see how you need loops if you call several different stored ptocs, and thus the resulting files are all expected to be different.


    Arthur

    MyBlog


    Twitter

    Sunday, May 10, 2015 1:47 AM
  • Hi Arthur,

    Yes, the files are different, but the loops are for making sure only n number of records are committed per batch. This is for recoverability in case there is an error on a large resultset. Then we can rollback on a smaller batch. Hope this makes sense.

    Thanks,

    Buster


    BusterCoder

    Monday, May 11, 2015 4:28 PM
  • For recoverability use the Checkpoint feature of SSIS

    Arthur

    MyBlog


    Twitter

    Monday, May 11, 2015 11:15 PM
  • Hi Arthur,

    Thanks again for a quick reply, but even though checkpoint files are kind of cool, implementing them is tricky. If you have a single-thread, straightforward Control Flow, they work. Anything parallel or looping, results are unpredictable. Remember, I'm generating these output files in parallel, as you observed in your earlier reply: 

    Moreover, have 5 Data Flow Tasks that are not connected to run in parallel.

    Buster


    BusterCoder

    Tuesday, May 12, 2015 4:30 AM