SSIS 2012 - passing variables from parent to child package

3
votes

I know this topic has been covered many times, but I have one caveat there that I can't seem to find an answer to .

I have several packages which all have ActivityDate variable. By default, the packages need to run for yesterday's date.

There are two possible scenarios.

Scenario 1 - packages are called from a master package. In this case, ActivityDate is set once, to yesterday's date, in the master package, and then passed down to child packages.

Scenario 2 - packages run stand-alone. In this case, ActivityDate is set in each child package, also to yesterday's date, via expression.

In SQL 2008 it was very easy - the variable in each child package vas set via Package Configuration for Scenario 1, but then used expression when run in Scenario 2.

I am unclear how to accomplish this in 2012. There is no more package configuration, so I need to create package parameter in the child package and then link to it from the parent. But parameters don't use expressions. So if I create ActivityDate parameter in the child package, then i can link to it from the parent (for Scenario 1), but I can't create an expression for it (for Scenario 2). And if I have only ActivityDate variable, then I can create an expression, but can't link to it from the parent.
So how do I make both scenarios work in 2012??

Help, please!

1
sqlssisssis-2012
If it's always set to yesterday, you could simply set it in each package and not worry about passing it through. – Hart CO
I assume you want flexibility to do things besides just yesterday, but otherwise @GoatCO is absolutely correct. – Kyle Hale
Kyle, you are right, of course I do want flexibility to run it for any other date. – Tamila

1 Answers

11
votes

First, parameter bindings can use expressions:

  1. Create a variable @User::Variable to hold the value you want to pass to the child package.
  2. Bind @User::Variable to your child parameter.

Secondly, instead of package configurations you have environment variables which perform the same basic function, setting values of parameters dynamically at runtime. The basic setup is:

  1. Define a project parameter and use it in your package.
  2. On the IS server, define an environment, set up an environment variable.
  3. Deploy your project and bind the environment variable to your project parameter.
  4. Execute package using particular environment reference.

Anyway, it's not entirely clear what you're trying to accomplish - by default use the parent activity date, but allow it to be overriden using a parameter? Or if you call the child package independently of the parent, still provide it with yesterday's value?

So, I think this accomplishes what you want:

  1. Create variable in Master Package User::ActivityDate. Set its value directly or using an expression.
  2. Create second variable in Master Package User::RunningFromMaster. Set as type Boolean and set default to True.
  3. Create parameter in Child Package $Parameter::MasterActivityDate bound to User::ActivityDate in Execute Package task.
  4. Create parameter in Child Package $Parameter::RunningFromMaster bound to User::RunningFromMaster in Execute Package task. Set as type Boolean and default to False.
  5. Create variable in Child Package User::ChildActivityDate.
  6. Set Expression property of User::ChildActivityDate to (@[$Parameter::RunningFromMaster] ? @$Parameter::MasterActivityDate : DATEADD("d",-1, GETDATE() ) )
  7. Use User::ChildActivityDate in your package.

When you run from master, User::ChildActivityDate will just take whatever value was passed in to the MasterActivityDate parameter. When your child package is run independently, it will just use the expression to pull yesterday's date (feel free to modify the expression to suit your specific needs.)

Also, if anyone asks, I used a Boolean because DateTime parameters always default to GETDATE() so you have to build convoluted conditionals to determine if they've been set or not.