Skip to main content

Intro to SSIS From SAP to SQL With Biml and Theobald

Why Use the Theobald Source From Biml​

Native support for Theobald Software's Xtract IS suite landed in the June 2018 Biml release wave, which made it possible to declare SAP sources directly in Biml instead of clicking through SSIS designers. There is no GetDatabaseSchema equivalent for the Theobald source yet, so column lists are still defined by hand in this introductory walkthrough. A more advanced pattern that drives the column list from the SAP DD03L data dictionary is covered in a follow up.

A Minimum Working Sample​

The package below uses one SAP source and one OLE DB destination to copy the MARA material master table into a staging table called 'SAP_MARA'.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="StagingDb"
      ConnectionString="data source=Your_SQL_Server;initial catalog=Your_Database;provider=SQLNCLI11.1;integrated security=SSPI;auto translate=False" />
    <TheobaldXtractSapConnection Name="SapErp"
      ConnectionString="USER=Your_UserName PASSWD=Your_Password LANG=EN CLIENT=800 ASHOST=Your_SAP_Server SYSNR=0" />
  </Connections>
  <Packages>
    <Package Name="LoadMaterialMaster" ProtectionLevel="EncryptSensitiveWithUserKey">
      <Tasks>
        <Dataflow Name="IMP MARA">
          <Transformations>
            <TheobaldXtractSapSource Name="Xtract Table" ConnectionName="SapErp"
              CustomFunction="Z_XTRACT_IS_TABLE_COMPRESSION" Table="MARA">
              <Columns>
                <Column SourceColumn="MANDT" SsisDataType="DT_WSTR" Length="3" />
                <Column SourceColumn="MATNR" SsisDataType="DT_WSTR" Length="18" />
                <Column SourceColumn="ERSDA" SsisDataType="DT_WSTR" Length="10" AbapType="d" />
                <Column SourceColumn="ERNAM" SsisDataType="DT_WSTR" Length="12" />
                <Column SourceColumn="MBRSH" SsisDataType="DT_WSTR" Length="1" />
                <Column SourceColumn="MATKL" SsisDataType="DT_WSTR" Length="9" />
                <Column SourceColumn="MEINS" SsisDataType="DT_WSTR" Length="3" />
                <Column SourceColumn="BSTME" SsisDataType="DT_WSTR" Length="3" />
                <Column SourceColumn="WRKST" SsisDataType="DT_WSTR" Length="48" />
                <Column SourceColumn="BRGEW" SsisDataType="DT_NUMERIC" Length="17" AbapType="p" Decimals="3" />
                <Column SourceColumn="NTGEW" SsisDataType="DT_NUMERIC" Length="17" AbapType="p" Decimals="3" />
                <Column SourceColumn="GEWEI" SsisDataType="DT_WSTR" Length="3" />
                <Column SourceColumn="VOLUM" SsisDataType="DT_NUMERIC" Length="17" AbapType="p" Decimals="3" />
              </Columns>
            </TheobaldXtractSapSource>
            <OleDbDestination Name="StgTarget" ConnectionName="StagingDb">
              <ExternalTableOutput Table="[SAP_MARA]" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Required Elements​

Two pieces of Biml carry the SAP integration:

  • 'TheobaldXtractSapConnection' takes a name and a connection string that holds the SAP user, password, language, client, host, and system number. Sensitive properties should be parameterized for production deployments.
  • 'TheobaldXtractSapSource' lives inside a Dataflow. At minimum it needs a name, the connection name, and the SAP table to read. Optional attributes cover the row delimiter, an order by clause, and the compression custom function shown above.

The column list uses SSIS data types ('DT_WSTR', 'DT_NUMERIC', and so on) along with the matching AbapType and Decimals attributes when the column needs them.

Where to Go Next​

A hand maintained column list does not scale beyond a couple of tables. The natural next step is to read the SAP DD03L data dictionary into a small metadata repository and let BimlScript generate both the staging tables and the load packages from that metadata. The follow up walkthrough covers that pattern.