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

Information about Biml.

Creating a Meta Data Driven SSIS Solution with Biml - 1. Overview

In this series of blog posts I’ll describe and walk you through the creation of a simple meta data driven SSIS solution using Biml.

Biml (Business Intelligence Markup Language) is an XML-based language that allows you to describe your BI Solution in a declarative fashion.

De Biml Package Generator is a new feature of BIDShelper a free add-in for the Microsoft Business Intelligence Development Studio (BIDS). It allows you to create packages based on the Biml documents. Furthermore you can use BimlScript to automate the creation of these packages.

I will use the Northwind sample database as the source for a simple Kimball based data warehouse. (MyDwh) I will also use a staging database (MyDwh_st) and a meta database (MyDwh_meta) that will include a table with a source to target mapping that will drive the creation of SSIS packages.

 

The series will consists of the following posts:

  1. Overview (This page)
  2. Setting the stage: Primarily a description of the SQL server databases and SQL statements to create the environment.
  3. Creating a TablePackage: Creating a Biml document to create a package to import one table.
  4. Creating the TablePackages: Using Bimclscript to automate the creation of packages
  5. Creating the MasterPackage
  • ma
    10
    okt 11

    SQL Bits 2011

    SQLBitsLogo

    From 29.09.2011 until 01.10.2011 I visited SQL Bits 9 in Liverpool. I had a great time: beautiful weather, well organized event and some great sessions… and I met some great- and nice people.

    In my own session “Creating a Meta Data Driven SSIS Solution with Biml” I tried to cover to much ground. I’ll try not to make that mistake again and focus more in depth on fewer aspects next time.

    You can download the slides of my session by clicking on the powerpoint logo.PowerPoint

  • za
    25
    jun 11

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

  • di
    21
    jun 11

    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]
    GO
    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
    UNION ALL
    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
    UNION ALL
    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
    UNION ALL
    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
    UNION ALL
    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
    UNION ALL
    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
    UNION ALL
    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
    UNION ALL
    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
    UNION ALL
    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
    UNION ALL
    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
    UNION ALL
    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:

    clip_image001

     

    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="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="Northwind"
                                  ConnectionString="Data Source=.;Initial Catalog=Northwind;Provider=SQLNCLI10.1;Integrated Security=SSPI;"
                                  CreatePackageConfiguration="true"></OleDbConnection>
                 <OleDbConnection Name="stagingDB"
                                  ConnectionString="Data Source=.;Initial Catalog=MyDwh_st;Provider=SQLNCLI10.1;Integrated Security=SSPI;"
                                  CreatePackageConfiguration="true"></OleDbConnection>
                 <OleDbConnection Name="DWH"
                                  ConnectionString="Data Source=.;Initial Catalog=MyDwh;Provider=SQLNCLI10.1;Integrated Security=SSPI;"
                                  CreatePackageConfiguration="true"></OleDbConnection>
           </Connections>
         <Packages>
             <# 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">
                       <!--Variables-->
                       <Variables>
                             <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>
                       </Variables>
                       <!--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=MyDwh_meta;Provider=SQLNCLI10.1;Integrated Security=SSPI;">
                                        </ConfigurationValue>
                                  </ConfigurationValues>
                             </PackageConfiguration>
                             <PackageConfiguration Name="<#=row["SourceConnection"]#>" ConnectionName="metaDB">
                                  <ExternalTableInput Table="[dbo].[SsisConfiguration]" />
                             </PackageConfiguration>
                             <#if (row["SourceConnection"].ToString()!=row["DestinationConnection"].ToString()) {#>
                            <PackageConfiguration Name="<#=row["DestinationConnection"]#>" ConnectionName="metaDB">
                                  <ExternalTableInput Table="[dbo].[SsisConfiguration]" />
                             </PackageConfiguration>
                             <#}#>
                      </PackageConfigurations>
                       <!--Tasks-->
                       <Tasks>
                             <!--Get Inital RowCount-->
                             <ExecuteSQL Name="SQL - Get Initial RowCount" ConnectionName="<#=row["DestinationConnection"]#>" ResultSet="SingleRow">
                                  <DirectInput>SELECT COUNT(*) AS RcStart FROM <#=row["DestinationName"]#></DirectInput>
                                  <Results>
                                        <Result Name="RcStart" VariableName="User.RcStart" />
                                  </Results>
                             </ExecuteSQL>
                             <# if((bool)row["TruncateDestination"]){ #>
                            <!--Truncate Table-->
                             <ExecuteSQL Name="SQL - Truncate Destination Table" ConnectionName="<#=row["DestinationConnection"]#>" ResultSet="None">
                                  <DirectInput>TRUNCATE TABLE <#=row["DestinationName"]#></DirectInput>
                             </ExecuteSQL>
                             <# } #>
                            <!--Get auditID-->
                             <ExecuteSQL Name="SQL - Create AuditRow and Get AuditID" ConnectionName="metaDB" ResultSet="SingleRow">
                                  <DirectInput>EXEC [dbo].[uspNewAuditRow] ?,?,?,?,?,?</DirectInput>
                                  <Parameters>
                                        <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" />
                                  </Parameters>
                                  <Results>
                                        <Result Name="0" VariableName="User.AuditID" />
                                  </Results>
                             </ExecuteSQL>
                             <!--Import Table-->
                             <Dataflow Name="DFT - <#=row["PackageName"]#>">
                                  <Transformations>
                                        <OleDbSource Name="ODS - <#=row["SourceConnection"]#>" ConnectionName="<#=row["SourceConnection"]#>">
                                              <DirectInput><#=row["SourceInput"]#></DirectInput>
                                        </OleDbSource>
                                        <RowCount Name="RC - Extracted Rows"  VariableName="User.RcExtract" />
                                        <DerivedColumns Name="DC - Add Metadata">
                                              <Columns>
                                                    <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>
                                              </Columns>
                                        </DerivedColumns>
                                        <OleDbDestination Name="ODD-BulkLoad <#=row["DestinationConnection"]#>" ConnectionName="<#=row["DestinationConnection"]#>">
                                              <ExternalTableOutput Table="<#=row["DestinationName"]#>"/>
                                              <ErrorHandling ErrorRowDisposition="RedirectRow" TruncationRowDisposition="RedirectRow" />
                                        </OleDbDestination>
                                        <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" />
                                        </OleDbDestination>
                                        <RowCount Name="RC - Error Rows" VariableName="User.RcError">
                                              <InputPath OutputPathName="ODD-RowbyRow <#=row["DestinationConnection"]#>.Error" />
                                        </RowCount>
                                        <RawFileDestination Name="RTF - Error File">
                                              <FileFromVariableOutput VariableName="User.ErrorFile"/>
                                        </RawFileDestination>
                                  </Transformations>
                             </Dataflow>
                             <!--Get Final RowCount-->
                             <ExecuteSQL Name="SQL - Get Final RowCount" ConnectionName="<#=row["DestinationConnection"]#>" ResultSet="SingleRow">
                                  <DirectInput>SELECT COUNT(*) AS RcEnd FROM <#=row["DestinationName"]#></DirectInput>
                                  <Results>
                                        <Result Name="RcEnd" VariableName="User.RcEnd" />
                                  </Results>
                             </ExecuteSQL>
                             <!--Update Audit Row-->
                             <ExecuteSQL Name="SQL - Update Audit Row" ConnectionName="metaDB" ResultSet="None">
                                  <DirectInput>EXEC [dbo].[uspUpdateAuditRow] ?,?,?,?,?,?</DirectInput>
                                  <Parameters>
                                        <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" />
                                  </Parameters>
                             </ExecuteSQL>
                       </Tasks>
                 </Package>
                       <# } #>
           </Packages>
    </Biml>

    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

     


     

     

  • ma
    20
    jun 11

    Creating a Meta Data Driven SSIS Solution with Biml - 3. Creating a Table Package

    In this third blogpost on creating a meta data driven ssis solution I'll create a biml document with which you can create a package that will import the contents of a query in the source database to a table in the destination database. In blogpost 4 I will enhance this document to include biml script that loops through the entries in the meta data table SsisPackages and create a package for every entry  in this table.

    The design of the TablePackage is loosely based on the design used by Joy Mundy and Warren Thornthwaite in their book:
    The Microsoft Data warehouse Toolkit With SQL Server 2008 R2. So it will include a system for logging results, adding lineage to the imported rows in the data warehouse and creates error files if appropriate.

    The Control Flow

    The control flow consist of: image

    1. An Execute SQL Task, retrieving the number of rows in the destination table at the start of the package.
    2. (Optional, depends on a setting in metadata table) An Execute SQL Task, Truncating the destination table.
    3. An Execute SQL statement to the meta database, creating a new row in the SsisAudit table and returning the AuditID of the created row. Which is used for Lineage in the Data Flow.
    4. The Data Flow, see next paragraph
    5. An Execute SQL Task, retrieving the number of rows in the destination table at the end of the package.
    6. An Execute SQL Task to update the audit row with the end time and several row counters.

     

     

     

     

     

     

     

     

     

    imageThe Data Flow

    The data flow will first try to import the data with a bulk-load setting (step 4). It will try to load any error rows using a row by row setting. (step5) Any remaining errors rows will be written to a raw text file.(step 7) In the data flow we will also count the extracted rows(step 2) and any error rows (step 6). The package will also add the meta data (AuditID and time stamps) to the data flow (step 3)

     

     

     

     

     

     

     

     

     

     

     

     

     

    The Biml code

    <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="Northwind"
                             ConnectionString="Data Source=.;Initial Catalog=Northwind;Provider=SQLNCLI10.1;Integrated Security=SSPI;"
                             CreatePackageConfiguration="true"></OleDbConnection>
            <OleDbConnection Name="stagingDB"
                             ConnectionString="Data Source=.;Initial Catalog=MyDwh_st;Provider=SQLNCLI10.1;Integrated Security=SSPI;"
                             CreatePackageConfiguration="true"></OleDbConnection>
            <OleDbConnection Name="DWH"
                             ConnectionString="Data Source=.;Initial Catalog=MyDwh;Provider=SQLNCLI10.1;Integrated Security=SSPI;"
                             CreatePackageConfiguration="true"></OleDbConnection>
        </Connections>
        <Packages>
            <Package Name="Import_Northwind_Categories" ConstraintMode="Linear" AutoCreateConfigurationsType="None">
                <!--Variables-->
                <Variables>
                    <Variable Name="AuditID" DataType="Int32" Namespace="User" >0</Variable>
                    <Variable Name="PackageID" DataType="Int32" Namespace="User" >99</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">c:\myErrorFile.raw</Variable>
                </Variables>
                <!--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="Northwind" ConnectionName="metaDB">
                        <ExternalTableInput Table="[dbo].[SsisConfiguration]" />
                    </PackageConfiguration>
                    <PackageConfiguration Name="stagingDB" ConnectionName="metaDB">
                        <ExternalTableInput Table="[dbo].[SsisConfiguration]" />
                    </PackageConfiguration>
                    <PackageConfiguration Name="DWH" ConnectionName="metaDB">
                        <ExternalTableInput Table="[dbo].[SsisConfiguration]" />
                    </PackageConfiguration>
                </PackageConfigurations>
                <!--Tasks-->
                <Tasks>
                    <!--Get Inital RowCount-->
                    <ExecuteSQL Name="SQL - Get Initial RowCount" ConnectionName="stagingDB" ResultSet="SingleRow">
                        <DirectInput>SELECT COUNT(*) AS RcStart FROM [dbo].[Northwind_Categories]</DirectInput>
                        <Results>
                            <Result Name="RcStart" VariableName="User.RcStart" />
                        </Results>
                    </ExecuteSQL>
                    <!--Truncate Table-->
                    <ExecuteSQL Name="SQL - Truncate Destination Table" ConnectionName="stagingDB" ResultSet="None">
                        <DirectInput>TRUNCATE TABLE [dbo].[Northwind_Categories]</DirectInput>
                    </ExecuteSQL>
                    <!--Get auditID-->
                    <ExecuteSQL Name="SQL - Create AuditRow and Get AuditID" ConnectionName="metaDB" ResultSet="SingleRow">
                        <DirectInput>EXEC [dbo].[uspNewAuditRow] ?,?,?,?,?,?</DirectInput>
                        <Parameters>
                            <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" />
                        </Parameters>
                        <Results>
                            <Result Name="0" VariableName="User.AuditID" />
                        </Results>
                    </ExecuteSQL>
                    <!--Import DIM_KOSTENPLAATS-->
                    <Dataflow Name="DFT - Import_Northwind_Categories">
                        <Transformations>
                            <OleDbSource Name="ODS - Northwind" ConnectionName="Northwind">
                                <DirectInput>SELECT CategoryID, CategoryName FROM [dbo].[Categories]</DirectInput>
                            </OleDbSource>
                            <RowCount Name="RC - Extracted Rows"  VariableName="User.RcExtract" />
                            <DerivedColumns Name="DC - Add Metadata">
                                <Columns>
                                    <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>
                                </Columns>
                            </DerivedColumns>
                            <OleDbDestination Name="ODD-BulkLoad stagingDB" ConnectionName="stagingDB">
                                <ExternalTableOutput Table="[dbo].[Northwind_Categories]"/>
                                <ErrorHandling ErrorRowDisposition="RedirectRow" TruncationRowDisposition="RedirectRow" />
                            </OleDbDestination>
                            <OleDbDestination Name="ODD-RowbyRow stagingDB" ConnectionName="stagingDB" UseFastLoadIfAvailable="false" >
                                <InputPath OutputPathName="ODD-BulkLoad stagingDB.Error" />
                                <ExternalTableOutput Table="[dbo].[Northwind_Categories]"/>
                                <ErrorHandling ErrorRowDisposition="RedirectRow" TruncationRowDisposition="RedirectRow" />
                            </OleDbDestination>
                            <RowCount Name="RC - Error Rows" VariableName="User.RcError">
                                <InputPath OutputPathName="ODD-RowbyRow stagingDB.Error" />
                            </RowCount>
                            <RawFileDestination Name="RTF - Error File">
                                <FileFromVariableOutput VariableName="User.ErrorFile"/>
                            </RawFileDestination>
                        </Transformations>
                    </Dataflow>
                    <!--Get Final RowCount-->
                    <ExecuteSQL Name="SQL - Get Final RowCount" ConnectionName="stagingDB" ResultSet="SingleRow">
                        <DirectInput>SELECT COUNT(*) AS RcEnd FROM [dbo].[Northwind_Categories]</DirectInput>
                        <Results>
                            <Result Name="RcEnd" VariableName="User.RcEnd" />
                        </Results>
                    </ExecuteSQL>
                    <!--Update Audit Row-->
                    <ExecuteSQL Name="SQL - Update Audit Row" ConnectionName="metaDB" ResultSet="None">
                        <DirectInput>EXEC [dbo].[uspUpdateAuditRow] ?,?,?,?,?,?</DirectInput>
                        <Parameters>
                            <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" />
                        </Parameters>
                    </ExecuteSQL>
                </Tasks>
            </Package>
        </Packages>
    </Biml>

    7. Create a package - and test it.

    Open Business Intelligence Development Studio and create a new Integration Services Project.image

    Right click  the project in solution explorer and select Add New Biml File.

    Rename the file to: Create a package.biml

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

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

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

    Test the package by Executing the package

  • ma
    20
    jun 11

    Creating a Meta Data Driven SSIS Solution with Biml - 1. Overview

    In this series of blog posts I’ll describe and walk you through the creation of a simple meta data driven SSIS solution using Biml.

    Biml (Business Intelligence Markup Language) is an XML-based language that allows you to describe your BI Solution in a declarative fashion.

    De Biml Package Generator is a new feature of BIDShelper a free add-in for the Microsoft Business Intelligence Development Studio (BIDS). It allows you to create packages based on the Biml documents. Furthermore you can use BimlScript to automate the creation of these packages.

    I will use the Northwind sample database as the source for a simple Kimball based data warehouse. (MyDwh) I will also use a staging database (MyDwh_st) and a meta database (MyDwh_meta) that will include a table with a source to target mapping that will drive the creation of SSIS packages.

     

    The series will consists of the following posts:

    1. Overview (This page)
    2. Setting the stage: Primarily a description of the SQL server databases and SQL statements to create the environment.
    3. Creating a TablePackage: Creating a Biml document to create a package to import one table.
    4. Creating the TablePackages: Using Bimclscript to automate the creation of packages
    5. Creating the MasterPackage
  • ma
    09
    mei 11

    Bimling with BIDS Helper

    BIDS is an abbreviation for Business Intelligence Development Studio. The area where Microsoft Business Intelligence developers and consultants spend a lot of hours working in.

    BIDS Helper is a free indispensable Visual Studio.Net add-in with features that extend and enhance the functionality of BIDS.

    Recently a new beta version (1.4.4.0) was released which includes the Biml package generator.

     

    Biml (Business Intelligence Markup Language) is an XML-based language that allows you to describe your BI solution in a declarative fashion.

    Today I took a test-drive and created my first package.

    BIML Editor

    The xml editor has some nice features: smooth intelliSense and blue curlylines to indicate possible errors.

    And when you right click the Biml document in Solution Explorer you can select Expand Biml File and the package(s) is/are created:

    BIML package

    Conclusion: a very promising addition to BIDS Helper and I'll enjoy diving into it more. Especially the possibility to use Biml scripts to automate package creation.