locked
Stopping a SSIS Foreach loop at a certain time. RRS feed

  • Question

  • I have a SSIS package that loops through ANSI 835 files, parses them, and posts the data to the appropriate database.  The issue I have is that sometimes this job can take some serious time and I need it over with at 6am (any remaining files can just wait until the next day).  It is unacceptable to use the SQL Agent to stop the package after 6am because the package may be in the middle of parsing a file and may only post partial data that our users would have to find and take time to clean up. 

    Is there a way to include a script task in my Foreach loop such that when it tries to process the next file the Script Task checks the time and if the time is after, say, 6am it stops the loop and reports success?

    I added a Script Task to our current package as a visual aid.  See screen shot in below link:

    ScreenShot

    JamesNT


    "If you have to ask about various operating system limits, you're probably doing something wrong." -Raymond Chen
    Sunday, November 14, 2010 6:28 PM

Answers

  • Maybe a For Each loop is not the exact fit here. Maybe a For loop is needed. With that task, you can manually manipulate the variables that are used for loop control. Have a script task inside the loop (first one or last one in the loop) that checks the time. If time is after 6:00 am, then have it set a variable that forces the loop to NOT execute any more.

    FYI: the For Each loop does not offer this type of granular loop control. IT simply loops, as its name implies FOR EACH item in the appropriate list.

    Hope this helps.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    • Proposed as answer by Reza RaadMVP Sunday, November 14, 2010 8:44 PM
    • Marked as answer by JamesNT Monday, November 15, 2010 10:58 PM
    Sunday, November 14, 2010 8:42 PM
  • Try using an expression for the File Spec property of the Foreach Loop, then in your last script task set the variable value to a filespec that doesn't exist in the target folder.

     

    • Marked as answer by JamesNT Monday, November 15, 2010 10:58 PM
    Sunday, November 14, 2010 8:50 PM

All replies

  • Maybe a For Each loop is not the exact fit here. Maybe a For loop is needed. With that task, you can manually manipulate the variables that are used for loop control. Have a script task inside the loop (first one or last one in the loop) that checks the time. If time is after 6:00 am, then have it set a variable that forces the loop to NOT execute any more.

    FYI: the For Each loop does not offer this type of granular loop control. IT simply loops, as its name implies FOR EACH item in the appropriate list.

    Hope this helps.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    • Proposed as answer by Reza RaadMVP Sunday, November 14, 2010 8:44 PM
    • Marked as answer by JamesNT Monday, November 15, 2010 10:58 PM
    Sunday, November 14, 2010 8:42 PM
  • Try using an expression for the File Spec property of the Foreach Loop, then in your last script task set the variable value to a filespec that doesn't exist in the target folder.

     

    • Marked as answer by JamesNT Monday, November 15, 2010 10:58 PM
    Sunday, November 14, 2010 8:50 PM
  • Todd,

    Nice idea.  I think I have just the thing to pull it off.  Give me some time to try this out and I'll provide feedback and "mark posts as answered where appropriate." 

    More news soon.

    JamesNT


    "If you have to ask about various operating system limits, you're probably doing something wrong." -Raymond Chen
    Monday, November 15, 2010 1:44 AM
  • I have used both techniques introduced by Todd and by Pendleton in different SSIS packages so both get credit.

    JamesNT


    "If you have to ask about various operating system limits, you're probably doing something wrong." -Raymond Chen
    Monday, November 15, 2010 10:59 PM
  • James,

    I am working on a similar project where I want to take an 835 file an import it into a database. I was trying to see your screen shot, but it is not available. Can you help me?

     

    Thanks,

    Sandor

    Tuesday, March 8, 2011 8:13 PM
  • I just tried it and it seems to work now.  Try again?

    JamesNT


    "If you have to ask about various operating system limits, you're probably doing something wrong." -Raymond Chen
    Wednesday, March 9, 2011 1:44 AM