Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

Subquery with Lookup transformation in SSIS

leave a comment »

Sometimes it is required to run additional subquery somewhere in a middle of the Data Flow Task. Suppose that we have some complex SQL select logic at the Data Flow Source when we initially getting hold of our data, then a few transformations down the way (maybe a conditional split or two, merge or union) and suddenly we realize that we need to support our next transformation logic based on the results of the subquery that we need to run exactly now, at this point and not at the source or anywhere before. I was looking into posts explaining on how to implement a subquery but they were all utilizing Script Component or stored procedure’s OUTPUT parameters.  Wouldn’t it be nice to implement this quite a common requirement on the fly without complex coding and writing stored procedures.

It can be done with Lookup transformation. Let’s assume that incoming data has Department ID column with unique values per row and we want to return number of counts for each Department ID in the flow. Open Lookup Transformation Editor and set No Cache option since we don’t need to cache the reference data set. Then, in Connection tab the following query is submitted.

select DepartmentID, count(*) DepartmentIDCount from OurTable

where DepartmentID = ‘000000’ –dummy department

group by DepartmentID

This query returns nothing and where clause submits the dummy Department ID. The query is required only for Lookup internal metadata generation which will allow us to connect input and referenced data sets in the Columns tab

The real query is provided on the Advanced tab and parameterized by Department ID


select DepartmentID, count(*) DepartmentIDCount from OurTable

group by DepartmentID where DepartmentID = ?


In the run-time a valid Department ID is provided and query returns the count of rows for each Department

This approach is not optimized since no caching involved, but can be suitable for a limited number of rows and in situations when Data Flow is complex or quick subquery is required.


Written by Konstantin Gorakine

March 4, 2010 at 4:18 pm

Posted in SSIS

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

%d bloggers like this: