Automate your SSIS from SAP to SQL with Biml and Theobald XtractIS

So a couple of days back, we’ve showed you how to load a table from SAP using SSIS, Theobald Xtract IS and Biml.

The issue with that approach was, that it would build fine but you would need to know your column’s datatypes etc. Today, we’ll take a look at how that metadata can be extracted from SAP!

The use of the Theobald Source in Biml requires a BimlStudio license – if you don’t have BimlStudio, you may want to get a demo license to follow along with our sample code.

Let’s get started by building a super easy metadata repository to control which tables and columns to load from SAP.
In production, you’ll want to use a more sophisticated model but once you get the general idea, it should be easy for you to modify and extend the model based on your needs.

We will use 3 tables:

– SAP_Tables
This is where we store a list of tables to get from SAP. We can also provide a custom_function to be used during the load – more on that later
– SAP_Columns
If you want to limit the number of columns per table, you can just add the columns to be loaded here. If you don’t define any columns for a table, it will be loaded completely (this is happening in the SAP_UseColumns view)
– SAP_DD03L
There is no need for you to maintain anything in this table. It will rather be a subset of what’s in the SAP table DD03L which is basically the SAP data dictionary!

The SAP_UseColumns view is also where the datatype mapping is happening – there are certainly still some special cases and glitches in there that need to be fixed but of course, we wanted to share this with you as soon as possible :)

CREATE DATABASE BimlDemo_Theobald
GO
USE [BimlDemo_Theobald]
GO
CREATE SCHEMA meta
GO
CREATE TABLE [meta].[SAP_Tables](
	[TABNAME] [NVARCHAR](30) NULL,
	[CUSTOM_Function] [NVARCHAR](50) NULL
) ON [PRIMARY]
GO
ALTER TABLE [meta].[SAP_Tables] ADD  CONSTRAINT [DF_SAP_Tables_CUSTOM_Function]  DEFAULT ('') FOR [CUSTOM_Function]
GO
CREATE TABLE [meta].[SAP_Columns](
	[TABNAME] [NVARCHAR](50) NULL,
	[FIELDNAME] [NVARCHAR](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [meta].[SAP_DD03L](
	[TABNAME] [NVARCHAR](30) NULL,
	[FIELDNAME] [NVARCHAR](30) NULL,
	[POSITION] [NVARCHAR](4) NULL,
	[INTTYPE] [NVARCHAR](1) NULL,
	[LENG] [NVARCHAR](6) NULL,
	[DECIMALS] [NVARCHAR](6) NULL
) ON [PRIMARY]
GO
CREATE VIEW [meta].[SAP_UseColumns] AS 
SELECT *, CASE WHEN inttype IN ('P') THEN 'Decimal'
          WHEN inttype IN ('C', 'N', 'D', 'T', 'F', 'X') THEN 'String'
          WHEN inttype IN ('I') THEN 'Int64' ELSE 'UNKNOWN' END BimlType
FROM(SELECT SAP.*
     FROM [meta].[SAP_DD03L] SAP
          INNER JOIN meta.sap_columns COL ON SAP.tabname=COL.tabname AND SAP.FIELDNAME=COL.fieldname
     UNION ALL
     SELECT SAP.*
     FROM [meta].[SAP_DD03L] SAP
          LEFT JOIN meta.sap_columns COL ON SAP.tabname=COL.tabname
     WHERE COL.tabname IS NULL)a;

Let’s also add some sample metadata. We will load a couple of columns from MARA as well as all columns from T001. For T001, we will use a custom_function. Our default is to load with compression but for T001, we want to disable that:

INSERT INTO [meta].[SAP_Tables] (TABNAME,Custom_Function) VALUES ('MARA','')
INSERT INTO [meta].[SAP_Tables] (TABNAME,Custom_Function) VALUES ('T001','Z_XTRACT_IS_TABLE')
INSERT INTO [meta].[SAP_Columns] (TABNAME,FIELDNAME) VALUES ('MARA','MANDT')
INSERT INTO [meta].[SAP_Columns] (TABNAME,FIELDNAME) VALUES ('MARA','MATNR')
INSERT INTO [meta].[SAP_Columns] (TABNAME,FIELDNAME) VALUES ('MARA','ERSDA')
INSERT INTO [meta].[SAP_Columns] (TABNAME,FIELDNAME) VALUES ('MARA','ERNAM')
INSERT INTO [meta].[SAP_Columns] (TABNAME,FIELDNAME) VALUES ('MARA','MBRSH')
INSERT INTO [meta].[SAP_Columns] (TABNAME,FIELDNAME) VALUES ('MARA','MATKL')
INSERT INTO [meta].[SAP_Columns] (TABNAME,FIELDNAME) VALUES ('MARA','MEINS')
INSERT INTO [meta].[SAP_Columns] (TABNAME,FIELDNAME) VALUES ('MARA','BSTME')
INSERT INTO [meta].[SAP_Columns] (TABNAME,FIELDNAME) VALUES ('MARA','WRKST')
INSERT INTO [meta].[SAP_Columns] (TABNAME,FIELDNAME) VALUES ('MARA','BRGEW')
INSERT INTO [meta].[SAP_Columns] (TABNAME,FIELDNAME) VALUES ('MARA','NTGEW')
INSERT INTO [meta].[SAP_Columns] (TABNAME,FIELDNAME) VALUES ('MARA','GEWEI')
INSERT INTO [meta].[SAP_Columns] (TABNAME,FIELDNAME) VALUES ('MARA','VOLUM')

With our metadata in place, let’s start writing some Biml.
Our first file is pretty straight forward: All we do is define our environment which consists of our SAP source, our target connection (including a database and schema definition) as well as the meta repository.
In this case, we’re keeping metadata and staged data in the same database but to make it easier to separate them, we’ve defined two different connections, both sharing the same connection string:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Connections>
		<Connection Name="SAP_Warehouse" ConnectionString="Data Source=.;Initial Catalog=BimlDemo_Theobald;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
		<Connection Name="Meta" ConnectionString="Data Source=.;Initial Catalog=BimlDemo_Theobald;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
		<TheobaldXtractSapConnection Name="SAP" ConnectionString="USER=Your_SAP_User PASSWD=Your_SAP_Pwd LANG=DE CLIENT=800 ASHOST=Your_SAP_Host SYSNR=0" />
	</Connections>
	<Databases>
		<Database Name="SAP_Warehouse" ConnectionName="SAP_Warehouse">
		</Database>
	</Databases>
	<Schemas>
		<Schema Name="dbo" DatabaseName="SAP_Warehouse">
		</Schema>
	</Schemas>
</Biml>

As mentioned before, SAP provides the required metadata to us in a table called DD03L.
Since this table is actually quite extensive and we won’t need metadata for every single SAP table, we will get a list of required tables from our repository and then query SAP for only these tables.
This will result in a package that populates our SAP_DD03L meta table with the data required for the following steps.
Otherwise, we’re using the same logic as in our initial Blog post on Theobald and Biml – if you did not see that, you may want to take a look to understand whats going on in this script:

<#@ template language="VB" optionexplicit="False" tier="10" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<# dim tbls as new List(of String)
	for each tbl in ExternalDataAccess.GetDataTable(Rootnode.Connections("Meta").RenderedConnectionString,"SELECT DISTINCT TABNAME FROM meta.sap_tables").Rows 
	tbls.add ("'" + tbl(0) + "'")
	next 
	#>
 <Packages>
    <Package Name="01 Read SAP Meta" ConstraintMode="LinearOnSuccess" ProtectionLevel="EncryptSensitiveWithUserKey" PackageSubpath="Prepare_SAP">
      <Tasks>
        <ExecuteSQL Name="TRUNCATE" ConnectionName="Meta">
          <DirectInput>TRUNCATE TABLE meta.SAP_DD03L</DirectInput>
        </ExecuteSQL>
        <Dataflow Name="Read DD03L">
          <Transformations>   
			<TheobaldXtractSapSource Name="Xtract Table" ConnectionName="SAP" CustomFunction="Z_XTRACT_IS_TABLE_COMPRESSION" Table="DD03L" WhereClause="TABNAME IN (<#= string.Join(",",tbls) #>)  and PRECFIELD  =''">
              <Columns>
                <Column SourceColumn="TABNAME" SsisDataType="DT_WSTR" Length="30" />
                <Column SourceColumn="FIELDNAME" SsisDataType="DT_WSTR" Length="30" />
                <Column SourceColumn="POSITION" SsisDataType="DT_WSTR" Length="4" AbapType="n" />
                <Column SourceColumn="INTTYPE" SsisDataType="DT_WSTR" Length="1" />
                <Column SourceColumn="LENG" SsisDataType="DT_WSTR" Length="6" AbapType="n" />
                <Column SourceColumn="DECIMALS"  SsisDataType="DT_WSTR" Length="6" AbapType="n" />
              </Columns>
            </TheobaldXtractSapSource>
            <OleDbDestination Name="Dest" ConnectionName="Meta">
              <ExternalTableOutput Table="[meta].[SAP_DD03L]" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

As we will need the result of the previous package in the next step, it is important that you build “01 Read SAP Meta” and execute it to make sure, SAP_DD03L is populated.

Once that is done, we can build a table-tag in Biml for every table including the columns and datatypes.
That whole logic should actually go into an extension method for better readability etc. (see also: Build your own extension method for Biml).

We will be looping over every table from our metadata, get the columns for it and create the required Biml tags including a couple of annotations:

<#@ template language="VB" optionexplicit="False" tier="20" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Tables><# for each tbl in ExternalDataAccess.GetDataTable(Rootnode.Connections("Meta").RenderedConnectionString,"SELECT TABNAME,max(CUSTOM_Function) CUSTOM_Function FROM meta.sap_tables  where tabname in (select tabname from meta.SAP_DD03L) group by TABNAME").Rows #>
		<Table Name="SAP_<#= tbl(0) #>" SchemaName="SAP_Warehouse.dbo">
			<Columns><# for each col in ExternalDataAccess.GetDataTable(Rootnode.Connections("Meta").RenderedConnectionString,"SELECT * FROM meta.sap_usecolumns where tabname = '" + tbl(0) + "' order by position").Rows 
					   tabname = col("TABNAME")
					   ssisType = "DT_WSTR"
					   length = int(col("leng"))  #>
				<Column Name="<#= col("FIELDNAME") #>" 
						<# if col("BimlType") = "String" then #>
						DataType="<#= col("BimlType") #>" Length="<#= length   #>" 
						<# elseif col("BimlType") = "Decimal" then
						   ssisType = "DT_NUMERIC"
						   length = int(col("leng")) + int(col("decimals")) +1
						   if length > 38 then length = 38#>
						 DataType="<#= col("BimlType") #>" Precision="<#= length  #>" Scale="<#= int(col("decimals")) #>"    
						<# elseif col("BimlType") = "Int64" then #>
						 DataType="<#= col("BimlType") #>"  
						<# else #>
							DataType="UNKNOWN TYPE: <#= col("inttype") #>"
						<# end if #>
							IsNullable="true">
					<Annotations>
						<Annotation AnnotationType="Tag" Tag="SAP_IntType"><#= col("IntType") #></Annotation>
						<Annotation AnnotationType="Tag" Tag="SsisDataType"><#= ssisType #></Annotation>
						<Annotation AnnotationType="Tag" Tag="Length"><#= Length #></Annotation>
					</Annotations>
				</Column><# next #></Columns>
			<Annotations>
				<Annotation AnnotationType="Tag" Tag="SAP_Name"><#= tabname #></Annotation>
				<Annotation AnnotationType="Tag" Tag="CUSTOM_Function"><#= tbl("CUSTOM_Function") #></Annotation>
			</Annotations>
		</Table><# next #></Tables>
</Biml>

Based on the tables that we’ve defined in the previous file, we have everything in place to create the target tables in our staging database – that code should look pretty familiar to you:

<#@ template language="VB" optionexplicit="False" tier="30" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Packages>
		<Package Name="02 Create Tables" ProtectionLevel="EncryptSensitiveWithUserKey" PackageSubpath="Prepare_SAP">
			<Tasks>
				<# for each tbl in RootNode.Tables #>
				<ExecuteSQL Name="Create <#= tbl.Name #>" ConnectionName="SAP_Warehouse">
					<DirectInput><#= tbl.GetDropAndCreateDDL #></DirectInput>
				</ExecuteSQL>
				<# next #>
            </Tasks>
        </Package>
    </Packages>
</Biml>

The last remaining step is the package to populate our staging tables. In this example, we’ll use a TRUNCATE/LOAD pattern and create on package for every single table.
All the required information is coming straight out of RootNode.Tables, either through native Biml properties or through some of the annotations we’ve set up:

<#@ template language="VB" optionexplicit="False" tier="30" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
		<# for each tbl in RootNode.Tables #>
        <Package Name="02_Load_<#= tbl.Name.Replace("/","_") #>" ConstraintMode="Linear" PackageSubpath="Load_SAP"  ProtectionLevel="EncryptSensitiveWithUserKey">
            <# 	custom_function = "Z_XTRACT_IS_TABLE_COMPRESSION"
				if tbl.gettag("CUSTOM_Function") <> "" then custom_function = tbl.gettag("CUSTOM_Function") #>
			<Tasks>
				<ExecuteSQL Name="TRUNCATE <#= tbl.Name #>" ConnectionName="SAP_Warehouse">
					<DirectInput>TRUNCATE TABLE [<#= tbl.Name #>]</DirectInput>
				</ExecuteSQL>
				<Dataflow Name="Read <#= tbl.GetTag("SAP_Name") #>">
					<Transformations>
						<TheobaldXtractSapSource Name="Xtract Table" ConnectionName="SAP" CustomFunction="<#= custom_function #>" Table="<#= tbl.GetTag("SAP_Name")#>">
							<Columns>
							<# for each col in tbl.Columns #>
								<Column SourceColumn="<#= col.Name #>" SsisDataType="<#=col.GetTag("SsisDataType") #>"  Length="<#=col.GetTag("Length") #>" <# if col.GetTag("SAP_IntType").ToLower <> "c" then #> AbapType="<#= col.GetTag("SAP_IntType").ToLower #>"<# end if#> <# if col.Scale > 0 then #> Decimals="<#= col.Scale #>"<# end if #>/>
							<# next #>
							</Columns>
						</TheobaldXtractSapSource>
						<OleDbDestination Name="Dest" ConnectionName="SAP_Warehouse">
							<ExternalTableOutput Table="[<#= tbl.Name #>]" />
						</OleDbDestination>
					</Transformations>
				</Dataflow>
			</Tasks>
            <Annotations>
                <Annotation AnnotationType="Tag" Tag="IsLoad">True</Annotation>
            </Annotations>
        </Package>
		<# next #>
		</Packages>
</Biml>

Thats’s it – have fun playing with that metadata and saving soooooooo much time :)

Any questions or comments? We’d love to hear from you at !

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

Happy Biml’ing!