Intro to SSIS from SAP to SQL with Biml and Theobald

Intro to SSIS from SAP to SQL with Biml and Theobald

One of the recent new features in Biml (included in the June 2018 release wave) is the native support for Theobald Software’s Xtract IS Suite.

While there are certainly features missing at this point (like support for GetDatabaseSchema), this is still a huge step forward!

To help you understand how the new integration works, let’s take a look at a little sample code:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
	<Connection Name="Stage" ConnectionString="data source=Your_SQL_Server;initial catalog=Your_Database;provider=SQLNCLI11.1;integrated security=SSPI;auto translate=False" />
    <TheobaldXtractSapConnection Name="SAP" ConnectionString="USER=Your_UserName PASSWD=Your_Password LANG=DE CLIENT=800 ASHOST=Your_SAP_Server SYSNR=0" />
  </Connections>
  <Packages>
	  <Package Name="My_Theobald_Package" ProtectionLevel="EncryptSensitiveWithUserKey">
      <Tasks>
        <Dataflow Name="IMP MARA">
          <Transformations>
            <TheobaldXtractSapSource Name="Xtract Table" ConnectionName="SAP" 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="OLE DB-Ziel" ConnectionName="Stage">
              <ExternalTableOutput Table="[SAP_MARA]" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Basically, all it takes is:

1. A TheobaldXtractSapConnection
It only needs a name and a connection string and is pretty self explaining.

2. A TheobaldXtractSapSource in your Dataflow
It requires at least a name, a referenced TheobaldXtractSapConnection as well as a table in SAP but also supports lots of other options like a delimiter, an order by clause or, as in our example a compression type.

In addition to that, you will need the columns to be used including their data types. Beware, those are SSIS Data Types – if you are struggling with these, I highly recommend the post SQL Server, SSIS and Biml Data Types by my friend and Biml Hero Cathrine Wilhelmsen.

Thats’s it – you can now build your package and load data from SAP to SQL – provided you have a valid Xtract IS license of course 🙂

Any questions or comments? We’d love to hear from you at biml@solisyon.de!

Further Information on Biml in English can be found on our English Biml Page.

Happy Biml’ing!

Facebook