Lookup Transformation

254 January 9, 2024
  •  The Lookup Transformation in SSIS is a powerful and useful SSIS transformation to compare the source and destination data. It filters out the matched and unmatched data in the specified destinations..

Procedure:-

    • Creating package under Project “doc”.
    • Under Solution Explorer we need to rename the package as required. i.e. dtsx.
    • Drag and Drop the Data Flow Task from the toolbox to the control flow region. Add a name to it of your choice.

  • Double-click on the Data Flow Task. Drag and drop the OLEDB source from the toolbox menu.
  • Double-click on the OLEDB source a menu will pop up. Enter the Database server name in the connection manager then select the table which contains the data.

  • Next go the columns and check whether the displayed columns matched the tables in the selected SQL table. Then click OK.
  • Drag and drop the Lookup Transformation from the toolbox menu.

  • Double click on Lookup Tranforamtion. Select Full Cache Mode and select redirect rows to no match output.
  • Go to Connection and select the lookup table .

  • Go to columns and map atleast one column with a same data type like below..

  • Click on ok .
  • Drag and drop the 2 OLEDB Destinations from Other Destinations in SSIS Toolbox and drag and drop on the workspace.

  • Map the Lookup to  Destination. Select the “Lookup Match output” as shown the below. Click on Ok.

  • Map the Lookup to Destination. Select the “Lookup Nomatch output” as shown the below. Click on Ok.

  • Double-click on the OLEDB destination a menu will pop up. Create at run time.

  • Click on mappings and check the all mappings are mapped or not. Click on OK.
  • Double-click on the OLEDB destination1 a menu will pop up. Create at run time.
  • Click on mappings and check the all mappings are mapped or not. Click on OK.
  • We can now run the package with the run option present in the main menu. When we press the Start button then the package will start running, and you can view the status of your SSIS package.

    • Verify the output in the SQL Server. Go to SQL Server .

     

    Matched Output:-

    Nomatch Output:-