Clever Geek Handbook

SSIS 2012 - passing variables from parent to child package

I know this topic has been covered many times, but I have one caveat that I cannot find an answer to.

I have several packages that have an ActivityDate variable. By default, packages should run for yesterday's date.

There are two possible scenarios.

Scenario 1 - packages are called from the main package. In this case, the ActivityDate is set once, until yesterday, in the main package, and then passed to the child packages.

Scenario 2 - Packages run autonomously. In this case, ActivityDate is set in every child package and also to yesterday's date via expression.

In SQL 2008, this was very simple - a variable in each child package was set using the package configuration for scenario 1, but then used an expression at startup in scenario 2.

I don't understand how to do this in 2012. There is no more package configuration, so I need to create a package parameter in the child package and then link it to the parent. But parameters don't use expressions. So if I create an ActivityDate parameter in the child package, I can bind it to the parent (for scenario 1), but I cannot create an expression for it (for scenario 2). And if I only have an ActivityDate variable, I can create an expression, but I cannot bind it to the parent.
So how do I get both scripts to work in 2012?

Help me please!

+3


source to share


1 answer


First, parameter bindings can use expressions:

  • Create a @User :: Variable to store the value you want to pass to the child package.
  • Bind @User :: Variable for your child parameter.

Second, instead of package configurations, you have environment variables that perform the same basic function, dynamically setting parameter values ​​at runtime. Basic setup:

  • Define a project parameter and use it in your package.
  • On the IS server, define the environment, set the environment variable.
  • Expand the project and bind the environment variable to the project parameter.
  • Execute a package using a specific environment reference.

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



So, I think this accomplishes what you want:

  • Create a variable in the main package User :: ActivityDate. Set its value directly or using an expression.
  • Create a second variable in the main package User :: RunningFromMaster. Set the type to Boolean and set the default parameter to True.
  • Create a parameter in Child Package $ Parameter :: MasterActivityDate associated with user :: ActivityDate in the package task execution.
  • Create a parameter in Child Package $ Parameter :: RunningFromMaster associated with user :: RunningFromMaster in the Execute Package task. Set as type Boolean and default to False.
  • Create variable in Child Package User :: ChildActivityDate.
  • Set the Expression property for User :: ChildActivityDate to (@[$Parameter::RunningFromMaster] ? @$Parameter::MasterActivityDate : DATEADD("d",-1, GETDATE() ) )

  • Use User :: ChildActivityDate in your package.

When you run from the wizard, User :: ChildActivityDate will simply accept whatever value passed to the MasterActivityDate parameter. When your child package is run independently, it just uses an expression to output 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 set GETDATE () by default, so you need to build collapsed conditionals to determine if they were set or not.

+7


source






More articles:


All Articles