Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Accessing variables in a parent package

In SQL Server Integration Services (SSIS) variables have scope (this was not the case in the forerunner DTS).

A variable can be accessed from any container that is an ancestor of the container to which the variable is scoped. This means that, for example, if you declare a variable and scope it to a Sequence container than it can be used by any of the tasks within that Sequence container (remember the container hierarchy discussion: http://blogs.conchango.com/jamiethomson/archive/2004/12/13/445.aspx)?

 

We know that packages are themseves containers and also that child packages are just an extension of the container hierarchy so this should mean that any variable declared in a parent package should be accessible by any child package called from that parent package, right? The answer is "Yes, but...". Let me explain.

A child package CAN see variables declared in a parent package at run-time, but at design-time the child package does not know that it is going to be executed as a child package and therefore at that point in time cannot "see" the variable in a parent package. Therefore any attempt to use a variable declared in a parent package in a task will result in a design-time validation error:

20050901ErrorUsingVariable.JPG

You can get around this problem by using a script task to assign the value of the variable in the parent package to a variable scoped to a container in the local package. Like this:

20050901ScriptTask.JPG

This works because the script task doesn't validate the variable names declared in its ReadOnlyVariables & ReadWriteVariables properties at design-time.

You can read a bit more about accessing variables in different packages here: http://blogs.conchango.com/jamiethomson/archive/2005/03/17/1151.aspx.

-Jamie

UPDATE: Read this: http://blogs.conchango.com/jamiethomson/archive/2007/08/28/Beware-of-variable-usage-in-script-tasks.aspx regarding the use of ReadOnlyVariables/ReadWriteVariables properties.

Published Thursday, September 01, 2005 9:39 AM by jamie.thomson

Comments

 

Jim Howey's SQL Server Integration Services Notes said:

In DTS 2000, the execute package task offers the ability to pass variables into the child package, and...
December 21, 2005 7:59 PM
 

Vinod said:

I am a regular visitor to this blog site & got lot of technical stuff. Thanks to all.

To this blog, I want to post one question, Please do answer.

Question: In DTS, we have an option in the "Execute Package Task" that, through "Inner Package Global Variables" tab, we can able to see the global variables declared at the child package (I mean, the package to which I am trying to point through Execute Package Task). How can we achieve the same in SSIS? I want to create an "Execute Package Task" in SSIS & try to access the child package global variables that I am pointing to through this task & pass the values from Parent to child.

Please give me your thoughts on this.

December 4, 2007 2:59 PM
 

jamie.thomson said:

Vinod,

You should check out Parent Packae configurations. They're not the same as what DTS did but I think you'll be able to achieve what you want.

-Jamie

December 4, 2007 4:26 PM
 

Alok Singh said:

Hi Jamie,

I am working on an ETL project where I have 3-4 packages which extract data from some place and are self sufficient to run.

Now when I want to run all these packages together I wl have to put these into "Execute package task" but problem is , all of these packages share same variable names  i.e. Company,Startdate and Enddate.

Now, I have created these variables in parent package("Execute task packg") as well as child package, and I have set all variable values blank in child package.

Now when I set a parent package variable .. eg: Company='XX',Startdate='01-jan-2008'  ,Enddate='01-Feb-2008'

It maps the variables prperly but it takes the values of inner variables ie "blank"

I cant rename inner variables as I have lots of expressions based on that , also I cant remove them as then I cant run the child packages individually without using "Execute Package task" .

I will try above solution ie to rename parent packg variable different from child package and then set the child pckg variable to parent.

But is der any other way around to solve this ...

April 14, 2008 1:06 PM
 

jamie.thomson said:

Alok,

This should work. The names of the variables should be irrelevant if you have set it up properly.

-Jamie

April 14, 2008 1:50 PM
 

Alok Singh said:

Yes , Its working for me ... Thanks alot :)

April 14, 2008 2:16 PM
 

Kaoru said:

I need to call 3 times the same child package in parallel but with different parameter.

How can I set those parameter if I can only read from the parent package? I actually run a execute process task and set the variables but I can't debug the child package.  

April 16, 2008 5:36 PM
 

jamie.thomson said:

Kaoru,

You've come across a limitation of the Execute Package Task. It has no equivalent of the /SET option of dtexec unfortunately.

The best option is to use parent package configurations instead.

-Jamie

April 17, 2008 3:59 AM
 

rabindra said:

Hi,

I have a parent package with script component task and also child package having script component.I have declared a varible in script component and taking the input as a form of variable with the help of inputbox in script component task.I want to pass these variable in child package and retain its value that I have entered in Parent Package .How can I do this?

April 17, 2008 4:20 PM
 

jamie.thomson said:

rabindra,

A few things:

1) You say "script component task". Do you mean "script component" or "script task"? there is a big difference.

2) Why are you trying to solicit information using an InputBox? SSIS is inherently run as a batch process, there won't be opportunity to prompt for that when it is running. use the /SET option of dtexec instead.

-Jamie

April 17, 2008 4:39 PM
 

Rabindra said:

Hi,

Sorry by Mistake,I typed script component  it is script task.I want to take input in the form of  variable that's y I am using script task.There is no source and Destination.Everything is in form of variable.Can I do this passing of variable in coding of script task  from parent to child package.

April 17, 2008 4:50 PM
 

Rabindra Jha said:

Hi,

I am running the child pacakge in the parent with help of script coding in script task  becuase I want to run the Package depending upon our choice  as in execute package task we can only run the pacakge in the mannar as we linked the package.So I took scrtipt task.Plz solve my issue..

April 17, 2008 5:04 PM
 

jamie.thomson said:

Rabindra,

If you want to pass a value stored in a variable into a child package then use parent package configurations.

-Jamie

April 17, 2008 8:46 PM
 

Rabindra Jha said:

Hi,

In coding of script task I have declared the variable like(dim ProductCode as string ).At the the package level I hve nt declared it and I am putting the value with in ProductCode with the help user input I want to use this value in coding of script task.So I want to know value of productCode from one package to another.At run time I want to import the value.

April 18, 2008 6:59 AM
 

jamie.thomson said:

I'm sorry, I simply don't understand what you are trying to do.

As I said above, if you want to pass a value from a parent package to a child package then use parent package configurations.

-Jamie

April 18, 2008 8:18 AM
 

Rabindra Kumar Jha said:

Hi,

Suppose there is two independent  package.I am not calling the child package with the use of execute Package Task rather than loading  and running the package with the help of script task.As you in script  task we are doing coing in vb.net script.just simply said how can import that script in another script task in another package.

can U suggest the code for this.

April 18, 2008 1:43 PM
 

jamie.thomson said:

So you want to share code in two places? If I understand you correctly then you can't really do that in SSIS unfortunately.

-Jamie

April 18, 2008 5:42 PM
 

Rabindra said:

Ya, u understand correctly.I have heard about script plus task we can do by script plus task Please see the link http://www.cozyroc.com/support/ssis_scriptinitview.html

April 19, 2008 9:00 AM
 

Rabindra Jha said:

In our Project our Database is Oracle.

                     We are using "Microsoft OLEDB Provider For ORACLE " as OLEDB connection manager.

                     We required one integer global Variable to be declared at the begining & need to access that variable throughout the package.

                     But the connection manager its not taking the integer variable , its taking only string variable.

June 2, 2008 12:24 PM
 

jamie.thomson said:

Rabindra,

Do you have a question?

-Jamie

June 2, 2008 2:35 PM
 

Rabindra Jha said:

is it posible na???The  connection manager take the integer variable also

June 9, 2008 12:57 PM
 

jamie.thomson said:

I don't understand. Declaring variables and accessing them has got nothing to do with connection managers.

What are you not telling me?

June 9, 2008 1:07 PM
 

Kunal said:

I am using your method of assigning the parent variable values to the child variables but it does not seem to work.

Does this not work if the parent variable gets dynamically populated in a For each Enumerator container

August 16, 2008 9:29 AM
 

jamie.thomson said:

Kunal,

I can't think why this would be a problem.

-Jamie

August 18, 2008 7:33 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems