Content #

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database that can be used to perform a broad range of data integration and data extraction, transformation, and loading (ETL) tasks.

Xtract IS is an SAP Integration solution for SSIS that provides all kinds of integration with SAP ERP and SAP BW in a secure and type-safe way with a graphical editor.

Business Intelligence Markup Language (Biml) is a domain-specific XML dialect for defining business intelligence (BI) assets. BIML authored BI assets can currently be used by the BIDS Helper add-on for Microsoft SQL Server Business Intelligence Development Studio (BIDS) and the Varigence Mist integrated development environment; both tools translate Biml metadata into SQL Server Integration Services (SSIS) and SQL Server Analysis Services (SSAS) assets for the Microsoft SQL Server platform.
However, emitters can be created to compile BIML for any desired BI platform, see Wikipedia.

In this article it is demonstrated how to use a BIML script to generate an SSIS package that extracts SAP table data using Xtract IS and loads it to an SQL Server.

In the BIML script I will define an SSIS Package (SSIS version 2014) with two connection managers and two tasks:

  • An OLE DB connection manager to an existing SQL Database BIMLTest and an Xtract Connection manager to an SAP ERP system.
  • An SQL Task to truncate the SQL table and a data flow task.

In the data flow task two components ae defined:

  • An Xtract Table component to extract three columns from the SAP customer table KNA1 .
  • An OLE DB Destination to load the extracted SAP data into the SQL Server table. The table should exist before, refer to the comments in the BIML script.

Step-by-step explanation #

Step 1: Add a new BIML File


Step 2: Write the BIML Script code


Here you can find the BIML Script Code. Check the requirements and the comments for more details.

Biml xmlns="">
  <!--   Requirements
  1. Create SQL-Database [BIMLTest]
  2. Create SQL-Table KNA1
      USE [BIMLTest]
      CREATE TABLE [dbo].[KNA1](
	      [KUNNR] [nvarchar](10) NULL,
	      [LAND1] [nvarchar](3) NULL,
	      [NAME1] [nvarchar](35) NULL
      ) ON [PRIMARY]
  3. SAP-User
  4. Further it is required that you have installed SSIS, Xtract IS and the BIDS Helper
  <!-- Connection Section  -->

  <Connections >
    <!-- SAP Connection
    Set the property ConnectionString  -->
    <CustomSsisConnection Name="SAPConnection" CreateInProject="1" CreationName="XTRACT" ObjectData="<XtractConnectionManager ConnectionString="USER=Elzein LANG=DE CLIENT=800 SYSNR=00 PASSWD=XXX RFCLIB=CLASSIC  " />"/>

    <!-- OLE DB Connection to SQL Server Database BIMLTest  -->
    <Connection Name ="OLE_BIML"
            ConnectionString="Data Source=.;Initial Catalog=BIMLTest;
                Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"/>

  <!-- Packages Section  -->
    <!-- Package Section  -->
    <Package Name="HelloWorld" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithUserKey">
      <!-- Tasks Section  -->
        <!-- Execute SQL Task Section to Truncate Table KNA1 -->
        <ExecuteSQL Name="(SQL) Truncate Table" ConnectionName="OLE_BIML">
          <DirectInput>TRUNCATE TABLE KNA1;</DirectInput>
        <!-- Data Flow Task Section to define the SAP source table and the SQL destination table -->
        <Dataflow Name="My Dataflow">
            <!-- Xtract Table component definition of table KNA1 -->
            <CustomComponent Name="KNA1"  ComponentTypeName="XtractIS.XtractSourceTable, XtractIS2014, Version=, Culture=neutral, PublicKeyToken=4b0cc842b94d345e">

                <Connection Name="Xtract SAP Connection" ConnectionName="SAPConnection" />

              <!-- customer properties of Xtract Table component -->
                <CustomProperty Name="MaxRows" DataType="Int32">0</CustomProperty>
                <CustomProperty Name="PackageSize" DataType="Int32">5000</CustomProperty>
                <CustomProperty Name="StringConversion" DataType="Int32">0</CustomProperty>
                <CustomProperty Name="UseCustomFunction" DataType="Boolean">false</CustomProperty>
                <CustomProperty Name="TableName" DataType="String">KNA1</CustomProperty>
                <!-- the InternalXML custom property defines the columns of the SAP source -->
                <CustomProperty Name="InternalXML" DataType="String">
                  <StorageTableBase xmlns="">
                  <ColDescription>Name 1</ColDescription>

              <!-- Output path of source component -->
                <OutputPath Name="XtractOutput" >
                    <OutputColumn Name="KUNNR" DataType="String" Length="10"/>
                    <OutputColumn Name="LAND1" DataType="String" Length="3"/>
                    <OutputColumn Name="NAME1" DataType="String" Length="35"/>
                    <ExternalColumn Name="KUNNR" DataType="String" Length="10"/>
                    <ExternalColumn Name="LAND1" DataType="String" Length="3"/>
                    <ExternalColumn Name="NAME1" DataType="String" Length="35"/>


            <!-- OLE DB Destination KNA1
            The table should exist.
            Column Names are the same in the source and in the destination.
            Mapping will be done automatically using the column names
            <OleDbDestination Name="OLE DB Destination" LocaleId="None" ConnectionName="OLE_BIML">
                <Annotation AnnotationType="Description">KNA1</Annotation>
              <ExternalTableOutput Table="KNA1" />


Related Links

Xtract IS - product information
Xtract IS - OnlineHelp