IN2BI, Microsoft Business Intelligence
data, stories & insights
rss | email | twitter

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:

Masterpackage

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="http://schemas.varigence.com/biml.xsd">
      <Connections>
            <OleDbConnection Name="metaDB"
                                   ConnectionString="Data Source=.;Initial Catalog=MyDwh_meta;Provider=SQLNCLI10.1;Integrated Security=SSPI;"
                                   CreatePackageConfiguration="true"></OleDbConnection>
            <OleDbConnection Name="msdb"
                                   ConnectionString="Data Source=.;Initial Catalog=msdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;"
                                   CreatePackageConfiguration="true"></OleDbConnection>
      </Connections>
      <Packages>
            <Package Name="MasterPackage MyDwh" ConstraintMode="Linear" AutoCreateConfigurationsType="None">
                  <!--Package Configurations-->
                  <PackageConfigurations>
                        <PackageConfiguration Name="MetaDB" >
                             <EnvironmentVariableInput EnvironmentVariable="Northwind_Config"></EnvironmentVariableInput>
                             <ConfigurationValues>
                                   <ConfigurationValue DataType="String"
                                                                 PropertyPath="\Package.Connections[metaDB].Properties[ConnectionString]"
                                                                 Name="metaDB"
                                                                 Value="Data Source=.;Initial Catalog=ROCWB_MIS_config;Provider=SQLNCLI10.1;Integrated Security=SSPI;">
                                   </ConfigurationValue>
                             </ConfigurationValues>
                        </PackageConfiguration>
                        <PackageConfiguration Name="msdb" ConnectionName="metaDB">
                             <ExternalTableInput Table="[dbo].[SsisConfiguration]" />
                        </PackageConfiguration>
                  </PackageConfigurations>
                  <!--Tasks-->
                  <Tasks>
                        <# 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">
                                         <Tasks>
                             <# }else { #>
                                   <!--Close container and start new container-->
                                         </Tasks>
                                   </Container>
                                   <Container Name="SEQC - <#=row["PackageGroup"]#>" ConstraintMode="Parallel">
                                         <Tasks>
                        <# }}#>
                                   <ExecutePackage Name="EPT - <#=row["PackageName"]#>">
                                         <SqlServer ConnectionName="msdb" PackagePath="\<#=row["PackageName"]#>" />
                                   </ExecutePackage>
                        <# }#>     
                             <!--Close container-->
                             </Tasks>
                        </Container>
                  </Tasks>
            </Package>
      </Packages>
</Biml>

 

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...

» Similar Posts

  1. Creating a Meta Data Driven SSIS Solution with Biml - 4. Creating the Table Packages
  2. Creating a Meta Data Driven SSIS Solution with Biml - 3. Creating a Table Package
  3. Making your Biml files less complex

» Trackbacks & Pingbacks

    No trackbacks yet.

» Comments

  1. Ludwig avatar

    Hi Marco,

    Great post. Thx.

    Is there a way to run a biml file as a sql-server job? I am wondering if a SQL-Server Job could run a a biml file that could create a whole bunch of packages and could could then be executed in subsequent steps...this opens up a lot of new interesting design alternatives..

    Thx

    L.

    Ludwig — juli 26, 2011 8:36
  2. Marco Schreuder avatar

    Hi Thomas,

    This option is not available in BIDS(Helper).

    However Biml is an invention of Varigence. (http://www.varigence.com)

    Maybe their product MIST has this option.

    I have passed your question to them.

    Greetinx

    Marco Schreuder — juli 26, 2011 8:40
  3. Marco Schreuder avatar

    Hi Thomas,

    I received the following answer from John Welch (http://agilebi.com/jwelch):

    Technically, this is possible. You can call the compiler programmatically or via a command prompt. So it’s very doable, just requires a little knowledge of what the compiler expects as input. I’m going to do a blog post on how to do this shortly.

    Greetinx

    Marco Schreuder — augustus 29, 2011 6:42
  4. Lee Sherry avatar

    Hi Marco,

    Great demo - I love it.

    However when I try to create the master package from the step 5 XML file, I get a compiler code error with little details on how to fix it.

    Do you have an updated XMLfile that works for step 5?

    Lee Sherry — december 7, 2011 2:00
  5. Samit Shah avatar

    Hi,

    When I created single package, it worked fine. But when I copied the code of Step 4. It was giving error during compilation. E.g. Character "#" hexadecimal 0x23 is illegal in xml name. Other error is missing attribute. Could you please help me.

    Thanks and Regards,

    Samit Shah

    Samit Shah — februari 6, 2012 10:00
  6. Marco Schreuder avatar

    Hi Samit,

    This is a known problem when copying to Visual Studio. John Welch describes the problem and a solution at:

    agilebi.com/.../configuring-vis

    Marco Schreuder — februari 8, 2012 9:52
  7. Samit Shah avatar

    Thanks Marco.

    When I set AutoCreateConfigurationType = "Sql" and AutoCreateConfigurationsTableName="dbo.SSIS_Configurations" and try to create SSIS Package. It prompts Could not resolve reference to the dbo.SSIS_Configurations of type 'Table'.AutoCreateConfigurationsTableName="dbo.SSIS_Configurations" is invalid. In recommendation, I am getting please provide valid scoped name. This table is present in DB. When I dropped the table and tried, it again gives me the same error.

    Thanks and Regards,

    Samit Shah

    Samit Shah — februari 9, 2012 10:11

Comments are closed