Use variables in various package

Question

Post reply

Use variables in various package

Helical Johan

Ten Centuries

Points: 1033
More actions
February 3, 2009 at 6:17 pm

#204910

Hi, I have a project and it have various package. Inside each package there are variables. What can I, for use the variable value package X inside package Y?
Is it possible?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

Scoping of variables at global or 'solution' level is not possible - so you have to find another way.

One way is to create a physical 'variables' table in SQL Server somewhere and use that for read/write of true globals.

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Robert Ham-354502 SSC Veteran Points: 234 More actions · Answer 2

In addition to using a variables table in a SQL database, you can use package configurations to share variable values among multiple packages. This works best if the variables remain static. Here are some links that discuss setting package variables based on package configurations (note that multiple packages can utilize the same configuration).

http://msdn.microsoft.com/en-us/library/ms141682(SQL.90).aspx

http://www.databasejournal.com/features/mssql/article.php/3509601/SQL-Server-2005---SQL-Server-Integration-Services---Part-3.htm

You can also pass variables from a parent package that uses the execute package task to run a child package.

Phil Parkin SSC Guru Points: 246959 More actions · Answer 3

Robert Ham (2/3/2009)
You can also pass variables from a parent package that uses the execute package task to run a child package.

So multiple child packages can read/write the same 'parent' variables - can you explain how in a bit more detail please?

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

TangoVictor SSChampion Points: 10975 More actions · Answer 4

I like the idea of persisting variables to a SQL table. You could use the event handlers. I can't remember the specific events but setup a routine to write the variable values to a table on exit and read the values on initialize. You could use the same code/components in all your packages.

Robert Ham-354502 SSC Veteran Points: 234 More actions · Answer 5

Here are some links for using Parent Package Variable configurations:

http://technet.microsoft.com/en-us/library/ms345179(SQL.90).aspx

http://www.sqlis.com/post/Using-Parent-Package-Variables-in-Package-Configurations.aspx

Note: you need to add a configuration for each variable you want to retrieve from the parent package in the child. What this does is assign the value of the variable in the parent package to a different variable in the child package, so changing the value of the child variable does not change the value of the parent variable. Good for read access, but not write.

However, when the parent package executes the child package, the parent package's variables become in scope for the child, since the execute package task is a container of the parent. Thus, you can reference, and update, the parent package's variables within the child package. This does have some limitations. You can only reference the parent's variables from within a script task in the child. Any other task type will fail validation. In addition, you will not be able to debug the child package independantly - since the tasks referencing the parent's variables will fail.

Here are some links for using script tasks to read/write parent package variables:

http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/29/passing-a-value-back-and-forth-from-a-parent-package-to-a-child-package-in-ssis.aspx

http://blogs.msdn.com/jamesk/default.aspx

I've also attached a brief demo of this (unzip to C:\Variable_Passing_Packages, or change the child package connection properties).

If you have packages which need to communicate with each other, but which cannot be configured as parent/child, then saving variable data in SQL server, or text files, is the easiest way I have seen.