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

Find ramblings

Wednesday, February 5, 2014

Biml - Execute Package Task (Package Deployment Model)

Biml - Execute Package Task (Package Deployment Model)

The Execute Package Task allows you to run a package from within another package. Insert Xzibit meme here.

This post will show how to execute a package that uses the classic package deployment model but is still deployed in the msdb. Specifically, we'll create a Variable to hold the package name and path, an OLE DB Connection Manager and a package with an Execute Package Task. The Execute package Task will use an Expression to specify the package for execution. I'm going to use one of the native data collector packages to ensure the package passes validation.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
        <AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=localhost\dev2012;Integrated Security=SSPI;Connect Timeout=30;Database=msdb;" Provider="SQL"  />

        <Package ConstraintMode="Linear" Name="Task_ExecutePackage_Package">
                <Variable Name="TargetPackage" DataType="String">\Data Collector\PerfCountersCollect</Variable>
                <ExecutePackage Name="EPT Run Package" DelayValidation="true">
                    <SqlServer ConnectionName="CM_OLE" PackagePath="Expression"></SqlServer>
                        <Expression PropertyName="PackageName">@[User::TargetPackage]</Expression>


Right click on the biml file and you should generate a package named Task_ExecutePackage_Package.dtsx.
As you can see, the Control Flow has an Execute Package task as promised.

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

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.

No comments: