Creating a Meta Data Driven SSIS Solution with Biml – 5. Creating the Master Package

In this last post in a series of 5 on creating a meta data driven ssis solution I”m going to create the master package. The master package will start the other table packages in the right order.

10. Deploy the Table Packages

Before you’ll create the master package publish the packages to sql server:

  • In the Solution Explorer (BIDS) right click the project and select Properties from the context menu.
  • Select Deploy under Configuration Properties in the treeview.
  • Change the DeploymentType to SqlServerDestination and the DestinationServer to localhost.
Project property Pages
  • In the Solution Explorer (BIDS) right click the project and select Deploy from the context menu.
The Master Package

I will group the Execute Package Tasks in Sequence Containers that correspond with the PackageGroup in the SsisPacakges Table.

Final result will look like this:

The Biml script

The Biml Script uses two connection: one to the meta database and one to the MSDB database where the packages are stored. And before we loop through the rows in the SsisPackages table and create the tasks I will use Package Configurations to handle future deployment issues. To create this package I’ll use the following script:

<#@ template language=”C#” hostspecific=”true”#>
<#@ import namespace=”System.Data” #>
<Biml xmlns=””>
            <OleDbConnection Name=”metaDB”
                                   ConnectionString=”Data Source=.;Initial Catalog=MyDwh_meta;Provider=SQLNCLI10.1;Integrated Security=SSPI;”
            <OleDbConnection Name=”msdb”
                                   ConnectionString=”Data Source=.;Initial Catalog=msdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;”
            <Package Name=”MasterPackage MyDwh” ConstraintMode=”Linear” AutoCreateConfigurationsType=”None”>
                  <!–Package Configurations–>
                        <PackageConfiguration Name=”MetaDB” >
                             <EnvironmentVariableInput EnvironmentVariable=”Northwind_Config”></EnvironmentVariableInput>
                                   <ConfigurationValue DataType=”String”
                                                                 Value=”Data Source=.;Initial Catalog=ROCWB_MIS_config;Provider=SQLNCLI10.1;Integrated Security=SSPI;”>
                        <PackageConfiguration Name=”msdb” ConnectionName=”metaDB”>
                             <ExternalTableInput Table=”[dbo].[SsisConfiguration]” />
                        <# string sPackageGroup=””; #>
                        <# string IsFirstRow=”True”; #>
                        <# string sConn = “Provider=SQLNCLI10;Server=.;Initial Catalog=MyDwh_meta;Integrated Security=SSPI;”; #>
                        <# string sSQL = “SELECT PackageGroup, PackageName FROM SsisPackages”; #>
                        <# DataTable tblPackages = ExternalDataAccess.GetDataTable(sConn,sSQL); #>
                        <# foreach (DataRow row in tblPackages.Rows){ #>
                        <# if(sPackageGroup!=row[“PackageGroup”].ToString()) { #>
                             <# sPackageGroup=row[“PackageGroup”].ToString();#>
                             <# if(IsFirstRow==”True”) { #>
                                   <# IsFirstRow=”False”; #>
                                   <!–Start new container–>
                                   <Container Name=”SEQC – <#=row[“PackageGroup”]#>” ConstraintMode=”Parallel”>
                             <# }else { #>
                                   <!–Close container and start new container–>
                                   <Container Name=”SEQC – <#=row[“PackageGroup”]#>” ConstraintMode=”Parallel”>
                        <# }}#>
                                   <ExecutePackage Name=”EPT – <#=row[“PackageName”]#>”>
                                         <SqlServer ConnectionName=”msdb” PackagePath=”\<#=row[“PackageName”]#>” />
                        <# }#>     
                             <!–Close container–>

11. Final steps: Create the masterpackage
  • In Solution Explorer (BIDS) right click the project and select Add New Biml File
  • Rename the Biml File to Create master package.biml
  • Copy the biml script above to the new biml file
  • In Solution Explorer (BIDS) right click the Create master package.biml file and select Generate SSIS Packages.
Final thoughts

In this series we created a complete SSIS Solution with

  • a logging and lineage method
  • package configurations to easily port the package to another environment (development, test, production)
  • an enforced standard way to import data: (Try Bulk-load, Try any error row RowByRow, and write any remaining errors to an error file)
  • a master package that can start the packages in the correct order

You could easily change this to fit your needs…