In an SAP table many fields can belong to the table key. In some scenarios when you extract an SAP table with Xtract IS to load it into an SQL table, you want to replace the SAP key fields by another custom key, e.g. a GUID.

The customer sales data (KNVV) table e.g. has the following fields as key:
MANDT: Client
KUNNR: Customer Number
VKORG: Sales Organisation
VTWEG: Distribution Channel
SPART: Division

In our scenario we want to load the customer sales data (KNVV) table into an SQL table but replace all SAP key fields by a GUID. Therefore we need a reference dataset, e.g. an SQL table, to perform lookups by joining data in SAP table columns with columns in our reference dataset. In our sample we will use the following lookup table CustomerSalesData_Lookup:

SELECT [Customer_GUID],
[MANDT],
[KUNNR],
[VKORG],
[VTWEG],
[SPART] FROM [DBDemo].[dbo].[CustomerSalesData_Lookup]

Be sure, that the input columns and lookup columns have matching data types.

Now let us design our SSIS Data Flow task:

  • create two connections, one to the SAP source system and one to the SQL destination
  • Add a Xtract Table source component to extract the SAP table KNVV. Select your desired columns and be sure that you selected the key fields.
  • Add a Lookup transformation. Select the connection manager and specify the lookup table.
    Use a drag-and-drop operation to map available input columns to lookup columns (MANDT, KUNNR, VKORG, VTWEG, SPART). Use the check boxes to select lookup columns (Customer_GUID) in the reference table.

Now the column Customer_GUID from the reference dataset is added to the transformation output. For more information refer to msdn: http://msdn.microsoft.com/en-us/library/ms141821.aspx

  • Add an OLE DB Destination to load the data into your destination table. Map the Lookup transformation output to your destination.

The following screenshoot shows our data flow task after it has been running. The upper data viewer shows the source SAP table. The lower data viewer shows how the lookup column is added to the output.

Your destination SQL table should now look like the following:

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.