locked
For Loop using SSIS Variables RRS feed

  • Question

  • I am trying to use a FOR LOOP to continue monthly processing. The SQL Task populates variables successfully and the control table is being updated as designed. When the process finishes it fails because there is no continuing timeframes to process.  How do I configure this loop to not fail once my variables stop being populated by the stored proc?


    KDW


    • Edited by siera_gld Sunday, January 10, 2016 3:49 AM
    Sunday, January 10, 2016 3:46 AM

Answers

  • Hi Kdw,

    According to your description, you want to know how to configure this loop to not fail once the variables stop being populated by the stored procedure, right?

    The For Loop is used for looping through a number of tasks for a set number of times. Base on my understanding, if you set 10 loop times, and the procedure only provide only 8 variables, then you will get this error. What you can do is using Foreach loop instead. The ForEach Loop will loop through various things such as files, objects, ADO connections. Please refer to the links below to see the details.
    https://ssisdevelopers.wordpress.com/2013/06/06/ssis-task-for-loop-vs-for-each-loop/
    http://bidn.com/Blogs/loops-in-ssis-what-rsquo-s-the-difference

    If I have anything misunderstood, please point it out.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Monday, January 25, 2016 8:46 AM
    • Marked as answer by Charlie Liao Tuesday, January 26, 2016 9:32 AM
    Sunday, January 17, 2016 5:47 AM

All replies

  • You can add a OnError flow from execute sql task to link to another task (may be a log table or something) which will make sure error gets logged and package exits without failure. One question here is why loop is not terminated if you've done with processing. Is your termination logic correct? Why should it again execute proc if it has no more values to return?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 10, 2016 5:22 AM
  • Hi Kdw,

    According to your description, you want to know how to configure this loop to not fail once the variables stop being populated by the stored procedure, right?

    The For Loop is used for looping through a number of tasks for a set number of times. Base on my understanding, if you set 10 loop times, and the procedure only provide only 8 variables, then you will get this error. What you can do is using Foreach loop instead. The ForEach Loop will loop through various things such as files, objects, ADO connections. Please refer to the links below to see the details.
    https://ssisdevelopers.wordpress.com/2013/06/06/ssis-task-for-loop-vs-for-each-loop/
    http://bidn.com/Blogs/loops-in-ssis-what-rsquo-s-the-difference

    If I have anything misunderstood, please point it out.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Monday, January 25, 2016 8:46 AM
    • Marked as answer by Charlie Liao Tuesday, January 26, 2016 9:32 AM
    Sunday, January 17, 2016 5:47 AM