Devart Lookup components are designed for joining the input data with the data from objects in the corresponding data source.
Note that Devart Lookup components support only the corresponding Devart connection managers.
To add a Devart Lookup component to a Data Flow task, perform the following steps:
| 1. | Drag the corresponding Devart Lookup component from the SSIS Toolbox window to the Data Flow diagram. |
| 2. | Connect the necessary data flow path to it. |
| 3. | Double-click the added component to open its editor. |
| 4. | On the Connection Managers tab select the corresponding Devart Connection Manager or create a new one. |
| 5. | In the Lookup Object (for cloud sources) or Lookup Table (for databases) list select the lookup object. |
| 6. | In the Lookup Columns list select check boxes for the columns to add to the Lookup match output. |
| 7. | In the Referential Constraint grid select the corresponding input columns and lookup key columns to match. |
| 9. | Connect the necessary lookup component outputs to other components. |
Devart Lookup components provide the following outputs:
| • | Lookup Match Output - processes rows that have at least one matched row in the lookup object/table. |
| • | Lookup No Match Output - processes rows that do not match to any of lookup object/table rows. |
| • | Lookup Error Output - by default this output is not used. You can set the NoMatchAsError property to true in order to send rows that do not match to any of lookup object/table rows to this output. |
|
Devart Lookup components use the following techniques to provide best performance for matching rows:
Batch Check
Devart Lookup components can check several input rows for matches at once with a single query to reduce the number of queries. You can set the maximal number of rows to check in a batch in the BatchSize custom property of the Lookup component.
Please note that assigning bigger values to this property increases the query size, and for some sources too large queries may cause errors. In case of errors reduce the value of this property.
This property and this functionality are not supported for Zoho CRM.
Advanced Caching
Devart Lookup components cache the queried rows from the lookup object/table, so if multiple input rows have the same lookup key values, Devart Lookup components uses cached rows instead of querying the source again. You can set the number of rows to cache in the CacheSize custom property of the Lookup component.
|
How to use Devart Lookup Components