It would appear that if a Child package is called more than once from a Parent using the 'Execute Package' task, then after the first execute the Parent Package Variables are not applied to child package. I.E we build dimensions in a master database and these are then loaded to a number of topic specific datamarts. We simply pass Parent variables to the child that hold source & target connection strings, the first time the package is called the correct database is accessed, subsequent Executes ignore the variables and use the original values. Manipulating the (our) event queue to run the package once results in the correct behaviour
Are packages cached when they are called from a Parent? if so is there a flag that I have missed to force a reload each time a child is executed?
This has just become a big problen for us so any guidance would greatly appreciated.
Paul
I have not come across this problem before. I suggest opening a support issue for this - it will likely need some debugging.
How are you calling the child package multiple times? In a loop?
Donald
|||Our master package is data driven, from a database.
Dimensions are build / modified in a Master database and then are moved into a number of topic specific datamarts. A dimension like CALENDAR natutally resides in most datamarts, we have a series of packages (one per Dimension) that are reponsible for moving new/changed data to the datamart (they are lightening quick). The child packages take 2 Parent variables SourceConnectionString and TargetConnectionString, these child packages are called from the parent package using the 'Execute Package' task.
This approach has worked well untill we got to serious testing of the system. When the Parent package runs properly it is quite likely that the calendar will move to several places in one execution. The first call to the Child package uses the parent variables correctly, subsequent calls to the child (event if 10 or 15 other packages have been called in the meantime) ignore the new settings of the variables and continue to adress the first datamart setting, consequently we have missing rows, the package does not fail, so the dependancy is met (we have dependacy info in our metadata) and the the Fact build crashes due to missing rows.
This serious stuff for us, we aim to go live at the end of this month
I too may have to hand over the armband....
Paul
|||
I do think you'll need to open a support call for the original issue.
However, I wonder if you could work around this. One way may be to use a different configuration type, such as SQL Server, or registry setting or environment variable.
Your master package could use a script to write out the value to that location and child packages would pick it up from there.
This would be very similar to using the parameter files that other ETL tools use for these scenarios.
Donald
If you do have to hand over the armband, try not to cry about it on TV.
|||Sorry, my fault.
The orginal version of the package was using Event Handlers, I removed these some time ago, but missed one which was setting an action as complete. So no bug in SSIS just my carlessness.
Just a thought, it would be really nice if icons on the design surface had some indication that and Event Handler was lurking underneath.
Paul
|||Fantastic idea. You should log it at Microsoft Connect.
It would also be great if there were a visual clue that properties had expressions on them!
-Jamie
No comments:
Post a Comment