Saturday, November 5, 2011

SSIS Lookup Control Implementation

SSIS[SQL Server Integration Services] Brief:
To work with SSIS we need to install SQL Server Business Intelligence Development studio.
When we open a package in the BIDS tool we get 3 tabs.
i)Control Flow.ii)Data Flow.iii)Event Handler.
In Control Flow section we do some control operation like Execute Task,Data Flow task,Foreachloop Container and so on and In Data Flow control we do data conversion,data transformation,data manipulation .

Here in the following I have demonstrate how to use Lookup control.
1>First take a Execute SQL Task in Control Flow tab.
2>Right click on the interface to add variable.

3>Right click on the Execute SQL Task. Select the connection. Select the source type. Here we can Put direct input as well as variable .In case of variable point the variable from the drop down.

4>Take a Data Flow task. Double click on it Data Flow control tab open.
5>In Data Flow control take a Excell Source. Right click on it and add the connection manager.

6> Select Colum from the left navigation. Select those column which we want to pass.

7>Now take a Data Conversion control. Right click on it and specify the destination data type.If Destination data type is varchar then it will be string[DT_STR]]
8>Now take a Lookup control select General. Specify Cache Mode as No Cache and Redirects Rows to match output.

9>Select Connection tab from left navigation. Specify the connection manager and chose the table on which the lookup will be perform.
10>Select Columns from the left navigation and specify which field are taken place for lookup comparison.

11>Now from the lookup control one destination is taken for match rows and another destination is Taken for unmatched rows.

12>In the above picture two derived column is taken. One derived column is taken to specify data conversion error and another is taken to specify data conversion error. One Union ALL control is taken Which accumulate all the error and sent to a flat file destination .

No comments: