Lookup Transformation in SSIS

Lookup Transformation

The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. You use the lookup to access additional information in a related table that is based on values in common columns.

You can configure the Lookup transformation in the following ways:

Select the connection manager that you want to use. If you want to connect to a database, select an OLE DB connection manager. If you want to connect to a cache file, select a Cache connection manager.
Specify the table or view that contains the reference dataset.
Generate a reference dataset by specifying an SQL statement.
Specify joins between the input and the reference dataset.
Add columns from the reference dataset to the Lookup transformation output.
Configure the caching options.

The Lookup transformation has the following inputs and outputs:

· Input.

· Match output. The match output handles the rows in the transformation input that match at least one entry in the reference dataset.

· No Match output. The no match output handles rows in the input that do not match at least one entry in the reference dataset. If you configure the Lookup transformation to treat the rows without matching entries as errors, the rows are redirected to the error output. Otherwise, the transformation would redirect those rows to the no match output.

· Error output.

Step 1: Drag and Drop Lookup component and go Edit mode.

Step 2: Select No Cache Mode ,Connection Type is OLE DB Connection and Redirect Rows to no match output.

Step 3: In the Connection Tab, select the OLE DB Connection for your database and select the SQL Query option. Please use the following query.

select LookUpValueID, v.value from Lookupvalue v inner join Lookup l on v.LookUPID = l.LookupID where l.Code ='Gender'

Step 4 : Connect the Match output to ADO.net Destination datasource and No Match output to the Flat file destination.

Step 5 : Execute the Package.

Step 6: After executing the package, see the results in SQL Server and the flat file.

Let me explain the comparison/matching behavior of lookup transformation: It matches data differently when in cached (default) and non-cached (Enable memory Restriction) mode. In cached mode comparison will be case sensitive where as in non-cached mode comparison will be based on collation level of column being matched.

Mode :: Behavior
Cached (default) :: Case sensitive
Non-cached (Enable memory restrictions) :: Collation level of column

Why?
It basically depends where the comparision is done. In cached mode SSIS reterives data from the source and comparision is done at the client on the byte level which is CASE SENSITIVE. Whereas in Non-Cache mode ( Enable memory Restriction) comparision is done at database and is based on the collation level of the column being matched.