Helpful information and examples on how to use SQL Server Integration Services.
Todays post is from Terri Chen, a developer on the SQL Server Integration Services team.
SSIS has introduced new concepts in SQL Server “Denali”, such as the project deployment mode, projects and parameters. We have extended Execute Package Task accordingly to best use parameters in child packages.
To pass configurations for child packages used to be quite some work and our customers used to complain a lot about it. With the new functionalities of Execute package Task, the experience with passing parameters for child packages is much simplified.
On opening the editor of Execute Package Task, you will find a new property, named ReferenceType on the “Package” page. If you choose “External Reference”, it will show the familiar SQL Server 2008 options of SQL Server or File System. All the scenarios are back compatible. The “Project Reference” is the new reference type.
After choosing, “Project Reference” as the reference type, you will see PackageNameFromProjectReference property with options of all package names in the project. Choose the targeted child package name. In the example, the child package is “ChildPacakge.dtsx”.
The child package in the example is a simple package with a single Execute Process Task. The value of RerquiredFullPathName property of the Execute Process Task is false. The executable expression uses a string type parameter called “ExcutableName”. The value of the parameter is “notepad”. By executing the child package itself, it invokes process “notepad”.
The “Parameter bindings” page is a new page introduced in In SQL Server “Denali”. It is used to bind a variable or a parameter of the parent package or a project level parameter with a parameter of the child package. During execution, the binding parameter or variable value is assigned to the parameter of the child package. In the example, the parent package has a string type parameter “ParentParameter” whose value is “calculator”. By clicking on “Add” button, a binding can be created as following. By executing the parent package, the value “calculator” is passed to the parameter “ExecutableName” of the child package and the child package will invoke process “calculator” instead of “notepad”.
ProjectReference is design for the new project deployment mode projects and only supports child packages in the same project as the parent package. If you want to reference a package in another project, you have to use External Reference and reference the package by its path on the file system or on the SQL Server. With external reference, you can only use configurations for child packages. Controls on “Parameter bindings” page is disabled when using external reference.