PRODUCT SALES

Requirement: Click here

Package Creation:

• Open BIDS.
• From data flow items drag and drop two OLEDB sources into designer surface (one is for ProdInfo table and the other one is for SaleInfo) and map with source tables.
• From data flow items drag and drop the “Merge Join” transformation in to designer surface.
• Merge join transformation accepts only sorted order data sets as input. So we need to sort the source data (based on common columns).
• Both the source tables are having “Prod_ID” common column.
• We can sort the source data in two ways

1. Using “Sort” transformation.
2. Using OLEDB source “Advanced Editor” option.

Note: If we use sort transformation then performance issue will accrue (if data volume is high). So we can use OLEDB source “Advanced Editor” option to sort the source data.

• Select OLEDB sources green arrow and connect to the “Merge join” transformation.

• Edit the “Merge Join” transformation and select the “Join type:” as “Left outer join”. And select the required columns. Find the below screen shot for your reference.

 

• Drag and drop “Derived column” transformation from “Data flow” items into designer surface. Connect the “Merge Join” transformation green arrow to the “Derived column” transformation.
• Create the required columns. Find the below screen shot for your reference.

 

• Drag and drop “OLEDB Destination” into designer surface from data flow items.
• Connect the “Derived column” transformation green arrow to the “OLEDB destination”.
• Configure the OLEDB destination with target table.

 

• Execute the package.

 

Target table Data: