none
Package Variable Values in For Each Contaner RRS feed

  • Question

  • I have a For Each File Container that loops through Excel files, executing a child package for each file found. I am using the RowCount transform to log the number of rows at various points in a DataFlow. When one file is processed the numbers are correct but when multiple files (e.g. 5) are processed some counts are zero when they shouldn't be. I have tried a 1 second sleep in the loop but that didn't fix the problem.

    R Campbell

    Thursday, February 26, 2015 12:05 PM

Answers

  • The sleep and SSIS do not go well together.

    It should not be related to timing, something is odd.

    I suggest you do deviate from the approach of using children packaged to loop.

    The looping thru the Excel files should be done within the ForEach loop in a package.

    I think you have overcomplicated the approach and face transient issues as a result.


    Arthur

    MyBlog


    Twitter

    I came to a similar conclusion myself and restructured everything into a single package with the For Each File container in that Control Flow layer of what was the child package. That fixed it. The original SSIS package was developed 7 or 8 years ago and I can't remember the reason for the parent/child structure. Perhaps it just seemed like a good idea at the time. Anyway testing and deployment is much simpler now as well. I was considering such a restructure for that reason alone.

    I really don't understand why the parent/child structure had issues with package variable values, surely each execution should be isolated from any that went before it as far as package variable values are concerned.

     

    R Campbell

    • Marked as answer by Dick Campbell Thursday, March 5, 2015 12:02 PM
    Tuesday, March 3, 2015 10:25 AM

All replies

  • Please share more technical details

    numbers of what where, what Data Flaw how a package reports on the counts?


    Arthur

    MyBlog


    Twitter

    Thursday, February 26, 2015 2:38 PM
    Moderator
  • Please share more technical details

    numbers of what where, what Data Flaw how a package reports on the counts?


    Arthur

    MyBlog


    Twitter

    Actually, the title IS misleading.

    I have a parent package with a For Each File container and within that a child package is called, passing the file path and name. The child package has a DataFlow with quite a few branches in the flow. On some of those branches I have RowCount "transforms" each linked to its own package variable.

    At the CotrolFlow level a script is executed immediately after the DataFlow which writes the package variable values to the dts log.

    If I place one file at a time into the folder that the For Each Container is looking at and wait for it to be processed, the numbers are correct. If I place a number of files (say 5) in the folder at one time, some of the numbers logged are correct and some are zero.

    I did try a 1 second sleep at the start of the logging  script but, when I think about it, the package variables probably being read before the script starts to execute. If this is a timing issue (which I don't think that it should be) it might need to be a separate script between the DataFlow and the existing script, with a sleep in it.

    Because each loop of the parent package executes a new instance of the child package I am puzzled as to how there can be a timing issue. No matter how tight the loop might be, a new instance of the child package is executed each time.


    R Campbell

    Friday, February 27, 2015 2:00 AM
  • Did you set to execute the children packages out of the process?

    Arthur

    MyBlog


    Twitter

    Friday, February 27, 2015 2:15 AM
    Moderator
  • Did you set to execute the children packages out of the process?

    Arthur

    MyBlog


    Twitter

    Do you means running the child package directly? If so the answer is no.

    It will be difficult emulate the problem scenario that way though because correct file path and name would have to be typed into a package variable each time making it impossible to execute the child package multiple times in rapid succession.

    I have comtemplated diespensing with the parent/child structure. moving the ForEach container and logging script into the ControlFlow of the child package, but I would expect that to make the problem worde if anything as the loop would actually becone even tighter.


    R Campbell

    Friday, February 27, 2015 2:28 AM
  • I have tried putting a sleep in a separate script prior to the logging script (which reads the package variables). It does seem to make a difference and the longer the sleep the more the package variable values are correctly reported, except for one particular Excel file.

    This is REALLY puzzling because if I present just this Excel file to the package, the package variable values are reported correctly. If I include it in a batch of 5 Excel files, all package variable values are reported as zero, even with a 60 second sleep between files. All the more puzzling is that each loop is a separate  execution of the child package so it is had to see how anything that happens with one package execution could affect subsequent executions.


    R Campbell

    Monday, March 2, 2015 9:39 PM
  • The sleep and SSIS do not go well together.

    It should not be related to timing, something is odd.

    I suggest you do deviate from the approach of using children packaged to loop.

    The looping thru the Excel files should be done within the ForEach loop in a package.

    I think you have overcomplicated the approach and face transient issues as a result.


    Arthur

    MyBlog


    Twitter

    Tuesday, March 3, 2015 12:37 AM
    Moderator
  • The sleep and SSIS do not go well together.

    It should not be related to timing, something is odd.

    I suggest you do deviate from the approach of using children packaged to loop.

    The looping thru the Excel files should be done within the ForEach loop in a package.

    I think you have overcomplicated the approach and face transient issues as a result.


    Arthur

    MyBlog


    Twitter

    I came to a similar conclusion myself and restructured everything into a single package with the For Each File container in that Control Flow layer of what was the child package. That fixed it. The original SSIS package was developed 7 or 8 years ago and I can't remember the reason for the parent/child structure. Perhaps it just seemed like a good idea at the time. Anyway testing and deployment is much simpler now as well. I was considering such a restructure for that reason alone.

    I really don't understand why the parent/child structure had issues with package variable values, surely each execution should be isolated from any that went before it as far as package variable values are concerned.

     

    R Campbell

    • Marked as answer by Dick Campbell Thursday, March 5, 2015 12:02 PM
    Tuesday, March 3, 2015 10:25 AM