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

Creating a Meta Data Driven SSIS Solution with Biml - 4. Creating the Table Packages

In part 3 of this series I created a Biml file and used it to create the desired SSIS package. In this part I am going to take it one step further: Í will use Biml script to loop through a number of rows in the SsisPackages table and create a package for every row.

Before we start creating the Biml script let's examine the SsisPackage table we created in part 2 and add some rows to it. The following columns are used in this table:

  • PackageID, a unique number identifying the package
  • PackageType, we will use 1 for this example. If you want to use another template you can use this column. If you have made manual changes to the package change this field into 0. This package won't be recreated.
  • PackageName, the name of the package
  • PackageGroup, this used to group related packages. e.g.: 1. Staging, 2 Dimensions etc. This will play an important role when we create the master package in part 5.
  • SourceInput, a sql statement that selects the rows in the source database.
  • SourceConnection, the name used as source connection
  • DestinationName, the name of the destination table.
  • DestinationConnection, the name used as destination connection.
  • ErrorFile, the full path and name for the raw error file.
  • TruncateDestination, a flag to indicate if you want to truncate the destination table.

Now, let's add some rows to this table...

8. Add rows to SsisPackages Table

Execute the following SQL query in Management Studio:

USE [MyDwh_meta]
TRUNCATE TABLE [dbo].[SsisPackages];
INSERT INTO [dbo].[SsisPackages] ([PackageType], [PackageName]
, [PackageGroup], [SourceInput], [SourceConnection], [DestinationName]
, [DestinationConnection], [ErrorFile], [TruncateDestination])
SELECT 1, N'Import_Northwind_Categories', N'1 Staging'
, N'SELECT CategoryID, CategoryName FROM [dbo].[Categories]'
, N'Northwind', N'[dbo].[Northwind_Categories]', N'stagingDB'
, N'c:\SSISErrors\Categories.raw', 1
SELECT 1, N'Import_Northwind_Customers', N'1 Staging'
, N'SELECT CustomerID, CompanyName, City, Country FROM [dbo].[Customers]'
, N'Northwind', N'[dbo].[Northwind_Customers]', N'stagingDB'
, N'c:\SSISErrors\Customers.raw', 1
SELECT 1, N'Import_Northwind_Employees', N'1 Staging'
, N'SELECT EmployeeID, LastName, FirstName, Title, ReportsTo FROM [dbo].[Employees]'
, N'Northwind', N'[dbo].[Northwind_Employees]', N'stagingDB'
, N'c:\SSISErrors\Employees.raw', 1
SELECT 1, N'Import_Northwind_OrderDetails', N'1 Staging'
, N'SELECT OrderID, ProductID, UnitPrice, Quantity, Discount FROM [dbo].[Order Details]'
, N'Northwind', N'[dbo].[Northwind_OrderDetails]', N'stagingDB'
, N'c:\SSISErrors\OrderDetails.raw', 1
SELECT 1, N'Import_Northwind_Orders', N'1 Staging'
, N'SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM [dbo].[Orders]'
, N'Northwind', N'[dbo].[Northwind_Orders]', N'stagingDB'
, N'c:\SSISErrors\Orders.raw', 1
SELECT 1, N'Import_Northwind_Products', N'1 Staging'
, N'SELECT ProductID, ProductName, SupplierID, CategoryID FROM [dbo].[Products]'
, N'Northwind', N'[dbo].[Northwind_Products]', N'stagingDB'
, N'c:\SSISErrors\Products.raw', 1
SELECT 1, N'Import_Northwind_Suppliers', N'1 Staging'
, N'SELECT SupplierID, CompanyName FROM [dbo].[Suppliers]'
, N'Northwind', N'[dbo].[Northwind_Suppliers]', N'stagingDB'
, N'c:\SSISErrors\Suppliers.raw', 1
SELECT 1, N'Import_DimCustomer', N'2 Dimensions'
, N'SELECT * FROM [dbo].[vwDimCustomer]'
, N'DWH', N'[dbo].[dimCustomer]', N'DWH'
, N'c:\SSISErrors\dimCustomer.raw', 1
SELECT 1, N'Import_DimEmployee', N'2 Dimensions'
, N'SELECT * FROM [dbo].[vwDimEmployee]'
, N'DWH', N'[dbo].[dimEmployee]', N'DWH'
, N'c:\SSISErrors\dimEmployee.raw', 1
SELECT 1, N'Import_DimProduct', N'2 Dimensions'
, N'SELECT * FROM [dbo].[vwDimProduct]'
, N'DWH', N'[dbo].[dimProduct]', N'DWH'
, N'c:\SSISErrors\dimProduct.raw', 1
SELECT 1, N'Import_FactOrder', N'3 Fact tables'
, N'SELECT * FROM [dbo].[vwFactOrder]'
, N'DWH', N'[dbo].[factOrder]', N'DWH'
, N'c:\SSISErrors\factOrder.raw', 1;

SELECT * FROM [dbo].[SsisPackages];


You will see the following results:



The Biml code to Create the packages

Next I will implement Biml.script to loop through these rows. Biml script is enclosed in the following tag: <# script #>

I used the xml code created in part 3 and changed it into:


<#@ 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="Northwind"
                              ConnectionString="Data Source=.;Initial Catalog=Northwind;Provider=SQLNCLI10.1;Integrated Security=SSPI;"
             <OleDbConnection Name="stagingDB"
                              ConnectionString="Data Source=.;Initial Catalog=MyDwh_st;Provider=SQLNCLI10.1;Integrated Security=SSPI;"
             <OleDbConnection Name="DWH"
                              ConnectionString="Data Source=.;Initial Catalog=MyDwh;Provider=SQLNCLI10.1;Integrated Security=SSPI;"
         <# string sConn = "Provider=SQLNCLI10;Server=.;Initial Catalog=MyDwh_meta;Integrated Security=SSPI;"; #>
         <# string sSQL = "SELECT * FROM SsisPackages WHERE PackageType=1"; #>
         <# DataTable tblPackages = ExternalDataAccess.GetDataTable(sConn,sSQL); #>
         <# foreach (DataRow row in tblPackages.Rows){ #>
            <Package Name="<#=row["PackageName"]#>" ConstraintMode="Linear" AutoCreateConfigurationsType="None">
                         <Variable Name="AuditID" DataType="Int32" Namespace="User" >0</Variable>
                         <Variable Name="PackageID" DataType="Int32" Namespace="User" ><#=row["PackageID"]#></Variable>
                         <Variable Name="RcStart" DataType="Int32" Namespace="User" >0</Variable>
                         <Variable Name="RcExtract" DataType="Int32" Namespace="User" >0</Variable>
                         <Variable Name="RcInsert" DataType="Int32" Namespace="User" >0</Variable>
                         <Variable Name="RcUpdate" DataType="Int32" Namespace="User" >0</Variable>
                         <Variable Name="RcError" DataType="Int32" Namespace="User">0</Variable>
                         <Variable Name="RcEnd" DataType="Int32" Namespace="User">0</Variable>
                         <Variable Name="ErrorFile" DataType="String" Namespace="User"><#=row["ErrorFile"]#></Variable>
                   <!--Package Configurations-->
                         <PackageConfiguration Name="MetaDB" >
                              <EnvironmentVariableInput EnvironmentVariable="Northwind_Config"></EnvironmentVariableInput>
                                    <ConfigurationValue DataType="String"
                                                                  Value="Data Source=.;Initial Catalog=MyDwh_meta;Provider=SQLNCLI10.1;Integrated Security=SSPI;">
                         <PackageConfiguration Name="<#=row["SourceConnection"]#>" ConnectionName="metaDB">
                              <ExternalTableInput Table="[dbo].[SsisConfiguration]" />
                         <#if (row["SourceConnection"].ToString()!=row["DestinationConnection"].ToString()) {#>
                        <PackageConfiguration Name="<#=row["DestinationConnection"]#>" ConnectionName="metaDB">
                              <ExternalTableInput Table="[dbo].[SsisConfiguration]" />
                         <!--Get Inital RowCount-->
                         <ExecuteSQL Name="SQL - Get Initial RowCount" ConnectionName="<#=row["DestinationConnection"]#>" ResultSet="SingleRow">
                              <DirectInput>SELECT COUNT(*) AS RcStart FROM <#=row["DestinationName"]#></DirectInput>
                                    <Result Name="RcStart" VariableName="User.RcStart" />
                         <# if((bool)row["TruncateDestination"]){ #>
                        <!--Truncate Table-->
                         <ExecuteSQL Name="SQL - Truncate Destination Table" ConnectionName="<#=row["DestinationConnection"]#>" ResultSet="None">
                              <DirectInput>TRUNCATE TABLE <#=row["DestinationName"]#></DirectInput>
                         <# } #>
                        <!--Get auditID-->
                         <ExecuteSQL Name="SQL - Create AuditRow and Get AuditID" ConnectionName="metaDB" ResultSet="SingleRow">
                              <DirectInput>EXEC [dbo].[uspNewAuditRow] ?,?,?,?,?,?</DirectInput>
                                    <Parameter Name="0" Direction="Input" DataType="Int32" VariableName="User.PackageID" />
                                    <Parameter Name="1" Direction="Input" DataType="Guid" VariableName="System.PackageID" />
                                    <Parameter Name="2" Direction="Input" DataType="Guid" VariableName="System.VersionGUID" />
                                    <Parameter Name="3" Direction="Input" DataType="Guid" VariableName="System.ExecutionInstanceGUID" />
                                    <Parameter Name="4" Direction="Input" DataType="DateTime" VariableName="System.StartTime" />
                                    <Parameter Name="5" Direction="Input" DataType="Int32" VariableName="User.RcStart" />
                                    <Result Name="0" VariableName="User.AuditID" />
                         <!--Import Table-->
                         <Dataflow Name="DFT - <#=row["PackageName"]#>">
                                    <OleDbSource Name="ODS - <#=row["SourceConnection"]#>" ConnectionName="<#=row["SourceConnection"]#>">
                                    <RowCount Name="RC - Extracted Rows"  VariableName="User.RcExtract" />
                                    <DerivedColumns Name="DC - Add Metadata">
                                                <Column Name="InsertAuditID" DataType="Int32" ReplaceExisting="false">@AuditID</Column>
                                                <Column Name="UpdateAuditID" DataType="Int32" ReplaceExisting="false">@AuditID</Column>
                                                <Column Name="RowStartDate" DataType="DateTime" ReplaceExisting="false">@StartTime</Column>
                                                <Column Name="RowEndDate" DataType="DateTime" ReplaceExisting="false">(DT_dbDATE)"12/31/9999"</Column>
                                    <OleDbDestination Name="ODD-BulkLoad <#=row["DestinationConnection"]#>" ConnectionName="<#=row["DestinationConnection"]#>">
                                          <ExternalTableOutput Table="<#=row["DestinationName"]#>"/>
                                          <ErrorHandling ErrorRowDisposition="RedirectRow" TruncationRowDisposition="RedirectRow" />
                                    <OleDbDestination Name="ODD-RowbyRow <#=row["DestinationConnection"]#>" ConnectionName="<#=row["DestinationConnection"]#>" UseFastLoadIfAvailable="false" >
                                          <InputPath OutputPathName="ODD-BulkLoad <#=row["DestinationConnection"]#>.Error" />
                                          <ExternalTableOutput Table="<#=row["DestinationName"]#>"/>
                                          <ErrorHandling ErrorRowDisposition="RedirectRow" TruncationRowDisposition="RedirectRow" />
                                    <RowCount Name="RC - Error Rows" VariableName="User.RcError">
                                          <InputPath OutputPathName="ODD-RowbyRow <#=row["DestinationConnection"]#>.Error" />
                                    <RawFileDestination Name="RTF - Error File">
                                          <FileFromVariableOutput VariableName="User.ErrorFile"/>
                         <!--Get Final RowCount-->
                         <ExecuteSQL Name="SQL - Get Final RowCount" ConnectionName="<#=row["DestinationConnection"]#>" ResultSet="SingleRow">
                              <DirectInput>SELECT COUNT(*) AS RcEnd FROM <#=row["DestinationName"]#></DirectInput>
                                    <Result Name="RcEnd" VariableName="User.RcEnd" />
                         <!--Update Audit Row-->
                         <ExecuteSQL Name="SQL - Update Audit Row" ConnectionName="metaDB" ResultSet="None">
                              <DirectInput>EXEC [dbo].[uspUpdateAuditRow] ?,?,?,?,?,?</DirectInput>
                                    <Parameter Name="0" Direction="Input" DataType="Int32" VariableName="User.AuditID" />
                                    <Parameter Name="1" Direction="Input" DataType="Int32" VariableName="User.RcExtract" />
                                    <Parameter Name="2" Direction="Input" DataType="Int32" VariableName="User.RcInsert" />
                                    <Parameter Name="3" Direction="Input" DataType="Int32" VariableName="User.RcUpdate" />
                                    <Parameter Name="4" Direction="Input" DataType="Int32" VariableName="User.RcError" />
                                    <Parameter Name="5" Direction="Input" DataType="Int32" VariableName="User.RcEnd" />
                   <# } #>

9. Create the packages - and test them.image

In BIDS right click the project in solution explorer and select Add New Biml File.

Rename the file to: Create the packages.biml

Copy the Biml code on this page to the newly created biml.file.

Right click the Create the packages.biml in Solution Explorer and select Generate SSIS Packages.

Open one of the newly created package Import_Northwind_Categories.dtsx and examine the contents.

Test the package by Executing the package




» Similar Posts

  1. Creating a Meta Data Driven SSIS Solution with Biml - 3. Creating a Table Package
  2. Creating a Meta Data Driven SSIS Solution with Biml - 5. Creating the Master Package
  3. Logging in SSIS with BIML 2 – Logging Package Execution in the Control Flow

» Trackbacks & Pingbacks

    No trackbacks yet.

» Comments

  1. Igor avatar

    Is there a way to set the "UseBulkLoadWhenPossible" flag to true on the <AdoNetDestination> ?

    I'm using Ado(Informix)->Ado(SqlS) and would like to use batch load (as you proposed).

    I think that is a feature of the AdoNetDestination added in 2008R2.

    Is there some kind a workaround, can I change some code or something?




    Ado->OleDb gives me the Unicode to Non-Unicode conversion error, so I gave up on that.

    On the other hand, I can't get Informix OLEDB to work in SSIS.

    Igor — september 19, 2012 1:56
  2. Marco Schreuder avatar

    Hi Igor,

    You can use:

    <AdoNetDestination Name="YourTarget" ConnectionName="YourConnection" UseFastLoadIfAvailable="true">


    Marco Schreuder — september 21, 2012 3:59

Comments are closed