A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Thursday, February 6, 2014

Biml - Execute Package Task (Project Deployment Model)

Biml - Execute Package Task (Project Deployment Model)

The Execute Package Task allows you to run a package from within another package.

This post will show how to execute a package that uses the new project deployment model. This biml generates two packages. The child package, Task_ExecutePackage_ChildPackage, will have a Variable which has an Expression based on a Package Parameter. The second package, Task_ExecutePackage_Project, will have an Execute Package Task that is driven by Variable for Package Name. It will also showcase how to use Parameter Bindings to pass values to child packages.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    Create a pair of packages using the 2012+ project deployment model.
    Task_ExecutePackage_ChildPackage will have a package parameter mapped used as an expression
    Task_ExecutePackage_Project will reference the 
        <Package ConstraintMode="Linear" Name="Task_ExecutePackage_ChildPackage">
                <Parameter DataType="String" Name="ParamValue">DesignTime</Parameter>
                <Variable DataType="String" Name="VariableValue" EvaluateAsExpression="true">@[$Package::ParamValue]</Variable>
        <Package ConstraintMode="Linear" Name="Task_ExecutePackage_Project">
                <Variable Name="TargetPackage" DataType="String">Task_ExecutePackage_ChildPackage.dtsx</Variable>
                <Variable Name="ParameterValue" DataType="String">ParentValue</Variable>
                <ExecutePackage Name="EPT Run Project Package" DelayValidation="true">
                    <ExternalProjectPackage Package="Task_ExecutePackage_ChildPackage.dtsx"></ExternalProjectPackage>
                        <ParameterBinding VariableName="User.ParameterValue" Name="ParamValue"></ParameterBinding>
                        <Expression PropertyName="PackageName">@[User::TargetPackage]</Expression>


Right click on the biml file and you should generate packages named Task_ExecutePackage_ChildPackage.dtsx and Task_ExecutePackage_Project.dtsx.


The child package does nothing except have a Variable named "VariableValue" which has an Expression based on the Package Parameter of "@[$Package::ParamValue]".


As you can see, the Control Flow has an Execute Package Task and two Variables..

Double click on the execute package task, click the Package tab and it should be configured as shown.

The Parameter Bindings tab shows the mapping between our local variable, ParameterValue, and the package's parameter name of ParamValue.

The package itself is driven by the Variable @[User::TargetPackage] which you can see if you click on the Expressions tab, expand the chevron on Expressions under Misc and you will see the PackageName property is powered by our Variable.

You can also look at Biml - Execute Package Task (Package Deployment Model)


Stef said...

Thanks Bill! Your posts rock and have saved me from tearing my hair out!

Mötz Jensen said...

You just saved my day! I had 8-10 packages that needed to be run in one go. Instead of creating several Sql Agent jobs / steps I was able to create a new "master" package that references all packages. Just need to create one Sql Agent job with one step and then I'm done.