Monday, May 11, 2015

How to use Look up in SSIS

Using lookup in SSIS:-
                Let me go with a scenario where we use lookup. The functionality of lookup is verifies the data coming from source to destination if the data is not available in destination. It will pass the data to other transformation or destination. If the same data coming from source it prevent the passage of data to other transformation. Configuration is given below Drag lookup transformation from the ssis tool box and configure to source and destinations. When you double click on lookup it will pop up configuration window like below picture

Cache mode: Full Cache for better performance (depends on requirement)
Connection type: OLEDB connection manager
Specify how to handle rows with no matched entries: Redirect rows to no matched output .later
Go to connection and select which table you want look up for existing data.
Go to columns option and check mappings in that for avoiding null inserts

I used adventure works for explaining look up transformation
Source table is  [Sales].[CreditCard]  and destination I selected in test database. [CreditCard] 
Step1:- open lookup transformation and specify how to move rows.

Step2:- go connection select which table you want to look up for existing data
Step3:- go to columns give reference mappings need to be compare source with destination. Like below window.

Click on ok. Drag ole dB destination and select input output selection. Select “Lookup no match output.”This option will transfer only not matched records .

Click ok execute,
When you execute it first time it transfer all the rows from source to destination, for second time it won’t transfer the rows.
1st time 
2nd time