The Lookup Component allows you to perform simple lookups on a table or view from an ADO.Net or OLEDB connection. This component is similar in function to the built-in SQL Server Integration Services Lookup task, but has the advantages of allowing you to bind keys and results to both variables and columns, working on both control-flow and data-flow surfaces, and allowing you precise control over caching. Specifically on this last point there is a significant issue with the standard component if you perform a lookup and then want to handle any lookup failure to create any missing data, as the standard component will permanently cache any lookup failure regardless of the cache settings.
To use this component, drag it onto a control-flow or data-flow surface, link any predecessor component and double-click to configure.
You simply select the connection, the table or view and the field bindings. You can bind each field on a Match basis, so that the contents of that column are matched, or on a Lookup basis, such that the contents of the column are retrieved. Match values can be specified as variables or columns. To match against a constant value you need to create a constant variable and use that. Lookup values can be stored in a variable or a column.
The Caching tab allows you to control the caching options, specifically whether to cache hits (where a match is found), misses (where a match is not found) or both, and the maximum size of the cache.
The Error Handling tab allows you to specify error-handling options, where no or multiple matches are found or data is truncated. This includes the ability to route error rows to the error output.
Where you are using the component on a control-flow, you can also handle the
OnLookupFailed event to handle a no-match scenario. This event is called after any NULL or default values are set, such that variable contents can be overriden as appropriate.
Note that there is also an
OnLookup event, for succesful matches.
On a data-flow, you can also configure an execution condition on the Condition
tab. This allows you to enter a simple expression to determine whether or
not the lookup should execute or be skipped for a particular row of the
data-flow. For example you could enter
[ContactID] != null to
execute the lookup only where the ContactID column does not contain a null
value. The condition syntax is essentially that of C#, C, C++, Java and
Javascript. and uses double equals (
==) to test for equality, double
ampersand for logical AND (&&) and double pipe for logical OR (||).
Conditions can include simple arithmetic expressions and variables, e.g.
[GrossProfit]/[Turnover]*100 > @MarginThreshold. Youc an enter column
names in [column] syntax and variables with a @ prefix, or you can use the
Insert Token link for assistance in embedding tokens.
All BlueSSIS data-flow components support properties 'Fail on truncation' and 'Unicode mode', allowing you to define applicable truncation and Unicode settings. These properties can be found in the Visual Studio/BIDS Properties window as shown below :
