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.
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.
Next, is the lookup cache transform. From within the transform, select ?Create New Connection Manager?.
This opens the connection manager. The first tab will display the columns from the lookup table.
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.
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.
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.
The expression editor opens. Click the ?Functions? tab in the upper left corner. This will show all the different categories of functions.
Expand ?Special?. In that folder, there is a LookupData function. Drag it and drop it into the expression window.
Next, add the parameters. The expression should look like this.
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.
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.
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!