locked
Problem with ForEach Loop and rowcount RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi all,

    I am hacking through the use of this tool so please bear with me. 

    I have a foreach loop that is looping through excel files.  I am processing one sheet at a time in each file.  So I process sheet 1 and if that was successful process sheet 2 etc.  If sheet1 fails I want the file written to a error directory.  In the dataflow for sheet1 I have a rowcount to the error handler.  In the control flow between sheet 1 and sheet 2, I branch based on the number of rows in the rowcount variable.

    It isn't working.  I branch in the controlflow if the dataflow truly errors but in the flow where I interrogate the rowcount variable it never goes to the the branch where rowcount > 0.   I have read that the rowcount is not completed until the entire dataflow is compelte.  Is that true for the foreach loop?  Some guidance and suggestions would be appreciated. 

    thanks,  SR

    The light at the end of the tunnel may be you.

    Monday, March 3, 2014 8:35 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi SuzSQL,

    You are doing quite complex things already.

    Yes, the rowcount value is not available until the loop is done.

    Consider changing you tactic

    Arthur My Blog

    • Proposed as answer by Mike Yin Tuesday, March 11, 2014 2:45 PM
    • Marked as answer by Mike Yin Thursday, March 13, 2014 2:52 AM
    Thursday, March 6, 2014 3:45 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Why not use event handler based on OnError event to capture this information. You can avoid package from failing by setting Propagate variable to false and continue with next sheet on failure. The failed sheet information with any other info (like rowcount) can be written to audit/control table inside OnError event handler

    see an example here

    http://visakhm.blogspot.in/2013/03/error-handling-in-ssis-loops.html

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Mike Yin Tuesday, March 11, 2014 2:45 PM
    • Marked as answer by Mike Yin Thursday, March 13, 2014 2:52 AM
    Thursday, March 6, 2014 4:45 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi SuzSQL,

    You are doing quite complex things already.

    Yes, the rowcount value is not available until the loop is done.

    Consider changing you tactic

    Arthur My Blog

    • Proposed as answer by Mike Yin Tuesday, March 11, 2014 2:45 PM
    • Marked as answer by Mike Yin Thursday, March 13, 2014 2:52 AM
    Thursday, March 6, 2014 3:45 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Why not use event handler based on OnError event to capture this information. You can avoid package from failing by setting Propagate variable to false and continue with next sheet on failure. The failed sheet information with any other info (like rowcount) can be written to audit/control table inside OnError event handler

    see an example here

    http://visakhm.blogspot.in/2013/03/error-handling-in-ssis-loops.html

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Mike Yin Tuesday, March 11, 2014 2:45 PM
    • Marked as answer by Mike Yin Thursday, March 13, 2014 2:52 AM
    Thursday, March 6, 2014 4:45 PM