Lookup Transform

In this particular case, the ETL team devised a job for converting the sql code for loading the order master table in its product with SSIS code. For the surrogate key upload, the ETL team used the lookup transform.

Reason for converting the code to SSIS:-

• ·         Increase the speed and ease for debugging the error and audit errors. The ETL team intends to add features such as auto correct load(re starting the job once the error takes place from the place the error has taken place)

• ·         Enable easy connectivity with large number of  ERPs
• ·         Help automate the process over a large number of deployments with SSIS.

  Attached blog indicates how one can convert multiple join used for ssis lookup transform loading        the Data warehouse into SSIS code.

The input sql code was converted to cte (or common table expressions). These common table expressions formed the procedure that needed to be converted into SSIS jobs.

Understand the joins between the tables and the required columns for developing the required dataflow.

·         Develop the code in SSIS for various join and using SSIS transforms as below:

In this section we indicate the join between the STG_DAYBOOK and Dim_DATE  based on order_date.

·         Pick up the relevant columns from ‘merge join’ after each join transformation.

 SSIS INPUT:-Using merge join transform:-

In this portion join with dim warehouse table get the relevant input:-

Develop the code in SSIS for various joins as below:

SSIS INPUT:-Using merge join transform:-

In this portion join with Dim_Supplier  table get the relevant input:-

SSIS input:-in this portion using the sort transform  on Dim_supplier table and above merge join  

Transform .

SSIS  input :- In this portion  use the merge join transform  based upon the partyledger name  and supplier name.

In this portion  of code is used for the get the unique  voucher type name  from stg_voucher_ type table.

For following condition : voucher  type  would be sales,purchase,journal(the specific voucher types have been extracted using conditional split)

Ssis input:-in this portion  use   the stg_voucher_type  table  and  aaply on  sort transform on this table get the unique voucher type name conditional split transform and apply the above  condition. The final sort was used to remove any duplicates that could arise.

SSIS:-Finally applying the SSIS  Lookup transform and combinebelow column one is available input columns

And  available lookup table based on the composite key and surrogate keys.

Final  table:-

Ssis input:-

The ETL team used the Full cache option to minimize the number of times the database shall be accessed. All the lookup values should be in the lookup cache .

The ETL team used the OLEDB connection manager since the lookup was been done from a local machine.

In case of no mtach the ETL team assumed a case of insert and hence the option of ‘Redirect rows to no match output’ was chosen.

The ETL team has assummed that there are no exceptions to be handled.

In case of ‘no match’ the output was put into another table(that is a copy of item supplier order as below:

The ‘execute sql task’ enables insertion of the non-matched rows into the target table of item supplier order.

Usage of sequucne container for completing the Lookup transformations:

The other tasks to be executed are ‘sending the mail’ etc.