I’m amazed at how many ETL developers do not know how to perform simple SQL Joins by using SQL Server Integration Services (SSIS) 2005. SSIS has several Dataflow components that can could be used accomplish this SQL task. Today I will focus on the Lookup Transformation component in SSIS.
“The Lookup transformation performs an equi-join between values in the transformation input and values in the reference dataset. Using an equi-join means that each row in the transformation input must match at least one row from the reference dataset. If there is no matching entry in the reference dataset, no join occurs and no values returned from the reference dataset. This is an error, and the transformation fails, unless configured to ignore errors or redirect error rows to the error output.” – Microsoft SQL Books Online
I will demonstrate how to use this feature and tool to your ETL advantage.
How do you perform a Left Outer Join using the SSIS Lookup component?
With only a few clicks, open up the Lookup Transformation Editor. Click on the “Configure Error Output” button and set the components setting to “Ignore failure”. This will cause the Lookup to find all matching values based on your query conditions and the remainder rows will have “null” where no match is found by the Lookup component.
Note: No action is required for the Error output stream.
How do you perform a Inner Join using the SSIS Lookup component?
Same process as above, but set the error output value to “Redirect row” failure.
The error output stream now will contain data for unmatched rows.
Note: It is not mandatory to place some destination for the error output. If you choose not to then the Lookup will always display a warning message.
Fig 2. Redirect Rows
That is all there is to it, for now..