February 22, 2006 at 8:03 am
I'm getting a child package to see the parent packages variables correctly, but I now want the child parent to UPDATE some of the parent package's variables. Things don't seem to travel in that direction.
Any suggestions on how to do this?
February 23, 2006 at 2:40 am
February 23, 2006 at 1:52 pm
Jamie,
I had a long reply all typed out and it got lost when I posted. Of course I didn't copy and paste to a text editor first. Hate it when that happens!
The upshot is this: somebody needs to clarify how to get SSIS parent variables updated through a child package. We need an example. This is fundamental stuff, and I can't find an example of how its done in BOL, in "Professional SQL Server 2005 Integration Services" (Wrox) (co-authored by Brian Knight of this site), in Webcasts, or blogs like yours. I can't even get the question answered on microsoft.public.sqlserver.dts.
From what I gather from your post and the posts linked to it, you have said that using the Package Confiurations to load the parent variable is like a ByVal reference - meaning updates to the child won't be reflected in the parent. To get a ByRef reference, you need to access the parent variables directly in the child's package. Step by step I assume it would look like this:
a. On the Parent package, create a package-scoped variable ParentVar.
b. Create Execute Package task pointing to the child package.
c. On the Child package, create a package-scoped variable NAMED DIFFERENTLY than the parent's variable, to avoid hiding it in the local scope. Call it ChildVar.
d. Create a Script task, and put ParentVar in the Read Variables and ChildVar in the Read/Write Variables box.
e. In the Script, have a line like the following:
Dts.Variables("ChildVar").Value = Dts.Variables("ParentVar").Value
f. Now ChildVar can be used and updated throughout Child package.
g. To copy ChildVar back to ParentVar, create another Script task with ChildVar in the Read Variables and ParentVar in the Read/Write Variables box.
h. Include a line like the following:
Dts.Variables("ParentVar").Value = Dts.Variables("ChildVar").Value
Well, when I try this I get a "Error: 0x2 at Child Script: The script threw an exception: Object reference not set to an instance of an object.".
So, please give the community a sample or a detailed step-by-step on this topic. We need something that can replace global DTS variables. Thanks!
Vince
February 23, 2006 at 2:33 pm
Well, just tried it again and it works. I don't know why I was getting the error last time, but I am able to update the parent's variables just fine. Whew!
Sorry for letting the frustration of this leak out. It cost me 2 days, but I did learn a lot about SSIS in the process...
November 11, 2008 at 5:41 am
@viacoboni : Hello viacoboni ! can you attach your example in this topic ? i have same problem , thanks
November 11, 2008 at 6:50 am
Sorry, much too long ago. I've not really worked much with SSIS since this post. But rereading my post seems like I provided detailed instructions. Have you tried them?
November 11, 2008 at 8:40 pm
thanks for your reply 🙂 i have tried do it but can't success . Have a success way is directly access by Dts.Runtime.Application to load the child package in script task of the parent package ( this way no need use Package Configurations anymore ) but i think it slow because need a connection
August 5, 2009 at 11:48 am
Could you specify more carefully which package the 2 scripts are in? I'm not sure if I need to put one in the Parent, one in the child, both in the parent, both in the child...
If this works, you've made history!
August 14, 2011 at 4:30 pm
After reading this and trying it out, I not only got it to work, I even understand it!
Even though it's an old topic, it helped me out a lot.
Thanks people!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply