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:
KUNNR: Customer Number
VKORG: Sales Organisation
VTWEG: Distribution Channel
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:
[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: