Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Disconnected Lookup in SSIS with Task Factory

  • 4 October 2012
  • Author: ShawnHarrison
  • Number of views: 11688
  • 0 Comments

One feature of Informatica that many people enjoy is the ability to do a ?disconnected lookup?. Now, thanks to the latest release of Task Factory, this is possible in SSIS! There a few, new, advanced tasks that can call functions using the SSIS expression language engine. In the following example, I will do a disconnected lookup using the Task Factory Advanced Conditional Split transform and use the results of the lookup as one of my conditions.

 

I have a package with two data flow tasks. The first one uses the Task Factory Advanced Lookup Cache transform to cache data from my lookup table, and the next data flow uses the Advanced Conditional Split.

image

 

 

Configure the Lookup Cache:

Inside the first data flow, I have an OLE DB source reading from my look up table (ProductVendor) and loading the data into the lookup cache. I am only selecting two columns from the table; one that I will use to join and the other I am going to return.

image

 

 

Next, is the lookup cache transform. From within the transform, select ?Create New Connection Manager?.

image

 

This opens the connection manager. The first tab will display the columns from the lookup table.

image

 

The next tab is used to configure the input parameters. The input parameter is the column you want to use for the join. Below that, you can add conditions. In this window, I am stating that the ProductID field is mapped to the ProductID input parameter and I am looking to return results in which the StandardPrice value is greater than 20.

image

 

Click ?OK?. That part is done. Now for the next piece.

 

The Advanced Conditional Split:

In the next data flow, I have an OLE DB source pulling from the Product table of the AdventureWorks database. I send that to the Advanced Conditional Split.

image

 

In the conditional split, I want to split my data based on the standard price found in the vendor table. First, I name the condition and then click the ellipses in the expression field.

 image

 

The expression editor opens. Click the ?Functions? tab in the upper left corner. This will show all the different categories of functions.

image

 

Expand ?Special?. In that folder, there is a LookupData function. Drag it and drop it into the expression window.

image

 

Next, add the parameters. The expression should look like this.

image

 

I start with the function (LookupData), and then reference the connection manager name (ProductCache) and then state what parameter I am using to join the data (ProductID). Remember, the parameter in this case is the input parameter from the lookup cache connection manager. Click ?OK? to return to the main editor.

image

 

Click ?OK? to close out the task. I am sending the results to two different terminator destinations. The conditional split will use the result of the disconnected lookup to split the data. It will return the column needed from the vendor table and then send products that have a price more than 20 to one output and all the other results will go to the default output.

image

 

That?s all there is to it. This is a new feature of most of the advanced tasks in the latest release of Task Factory. Enjoy!

Print
Tags:
Rate this article:
No rating

ShawnHarrisonShawnHarrison

Other posts by ShawnHarrison

Please login or register to post comments.