SQL Joins in SSIS Lookup Component

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.

Fig 1. Ignore Failure

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..

This entry was posted in SQL Server 2005 and tagged , . Bookmark the permalink.

2 Responses to SQL Joins in SSIS Lookup Component

  1. Sonia says:


    I hope it’s never too late to say ‘Thank’s’. I’ve just found this post and it was very helpful. Just a little suggestion, you may give the version of SSIS which is here used on your illustration…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s