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 blog I will show you 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 I will define two components:

– 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 1. Add a new Biml File biml-01

Step 2. Write the BIML Script code

biml-02

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

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <!--   Requirements
  1. Create SQL-Database [BIMLTest]
  2. Create SQL-Table KNA1
      USE [BIMLTest]
      GO
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE TABLE [dbo].[KNA1](
	      [KUNNR] [nvarchar](10) NULL,
	      [LAND1] [nvarchar](3) NULL,
	      [NAME1] [nvarchar](35) NULL
      ) ON [PRIMARY]
      GO
  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="&lt;XtractConnectionManager ConnectionString=&quot;USER=Elzein LANG=DE CLIENT=800 SYSNR=00 ASHOST=ec5.theobald-software.com PASSWD=XXX RFCLIB=CLASSIC  &quot; /&gt;"/>

    <!-- 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;"/>

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

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

              <!-- customer properties of Xtract Table component -->
              <CustomProperties>
                <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">
                  &lt;StorageTableBase xmlns=&quot;http://tempuri.org/StorageTableBase.xsd&quot;&gt;
                  &lt;TableColumns&gt;
                  &lt;ColName&gt;KUNNR&lt;/ColName&gt;
                  &lt;ColDescription&gt;Debitorennummer&lt;/ColDescription&gt;
                  &lt;ColLength&gt;10&lt;/ColLength&gt;
                  &lt;ColSelected&gt;true&lt;/ColSelected&gt;
                  &lt;ABAPType&gt;c&lt;/ABAPType&gt;
                  &lt;Decimals&gt;0&lt;/Decimals&gt;
                  &lt;/TableColumns&gt;
                  &lt;TableColumns&gt;
                  &lt;ColName&gt;LAND1&lt;/ColName&gt;
                  &lt;ColDescription&gt;L&#228;nderschl&#252;ssel&lt;/ColDescription&gt;
                  &lt;ColLength&gt;3&lt;/ColLength&gt;
                  &lt;ColSelected&gt;true&lt;/ColSelected&gt;
                  &lt;ABAPType&gt;c&lt;/ABAPType&gt;
                  &lt;Decimals&gt;0&lt;/Decimals&gt;
                  &lt;/TableColumns&gt;
                  &lt;TableColumns&gt;
                  &lt;ColName&gt;NAME1&lt;/ColName&gt;
                  &lt;ColDescription&gt;Name 1&lt;/ColDescription&gt;
                  &lt;ColLength&gt;35&lt;/ColLength&gt;
                  &lt;ColSelected&gt;true&lt;/ColSelected&gt;
                  &lt;ABAPType&gt;c&lt;/ABAPType&gt;
                  &lt;Decimals&gt;0&lt;/Decimals&gt;
                  &lt;/TableColumns&gt;
                  &lt;/StorageTableBase&gt;
                </CustomProperty>
              </CustomProperties>

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

            </CustomComponent>

            <!-- 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">
              <Annotations>
                <Annotation AnnotationType="Description">KNA1</Annotation>
              </Annotations>
              <ExternalTableOutput Table="KNA1" />
            </OleDbDestination>

          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

The properties of the Xtract table component can be later checked, when the package is generated.

biml-kna1-prop

The InternalXML property defines the structure of the table as follows:
<StorageTableBase xmlns="http://tempuri.org/StorageTableBase.xsd">
<TableColumns>
<ColName>KUNNR</ColName>
<ColDescription>Debitorennummer</ColDescription>
<ColLength>10</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>c</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
<TableColumns>
<ColName>LAND1</ColName>
<ColDescription>Länderschlüssel</ColDescription>
<ColLength>3</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>c</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
<TableColumns>
<ColName>NAME1</ColName>
<ColDescription>Name 1</ColDescription>
<ColLength>35</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>c</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
</StorageTableBase>

Step 3. Generate the SSIS Packagebiml-03

 

Step 4. Check the SSIS Package

The generated SSIS package contains two connection managners and two tasks.

biml-04

The data flow task contains the Xtract Table component as source and the OLE DB Destination

biml-05Step 5. Run the package now.

The package should now run successfully and load the data from the SAP table KNA1.

biml-06

You can check the result in the SQL Server table

biml-07

For futher information please check the following references:

Xtract IS

BIDS Helper – BIML Package Genearator

Written by Khoder Elzein

Khoder is responsible for presales and always has an attentive ear for our customers, prospectives and partners. When travelling in Germany or abroad, he provides customer support at PoCs, workshops and on training courses. He also looks after the further development of our software solutions. Khoder has been working in IT since the turn of the millennium; he has been a member of team Theobald since 2009. When it comes to private interests, family, nature and reading feature at the top of his list – along with innovative fusion cuisine, as you may infer from his favourite dish of Swabian cheese noodles with tabouleh.