SSIS Components for Microsoft Dynamics 365

Build 22.0.8509

Using the Lookup Component

After Establishing a Connection to the data source, you can use the CData Microsoft Dynamics 365 lookup component to do a lookup against Microsoft Dynamics 365 and map the matched and unmatched rows to different outputs in your Data Flow task.

Performing a Lookup to Microsoft Dynamics 365 Data with the Lookup Component

You can use the Lookup Component to perform a lookup on a specified set of columns in Microsoft Dynamics 365. This component takes one input and has two outputs: one for matched rows in Microsoft Dynamics 365 and one for unmatched rows. The component provides two cache options which can affect the performance of the lookup but do not change the results of the lookup operation. These cache options are described later in this section.

  1. In the SSIS Toolbox, drag the CData Microsoft Dynamics 365 Lookup Component into the Data Flow.
  2. Double-click the CData Microsoft Dynamics 365 Lookup Component. The CData Microsoft Dynamics 365 Lookup Component opens.
  3. Navigate to the Connection tab. In the Connection drop-down list, select an available CData Microsoft Dynamics 365 connection manager, or create a new instance if one is not already available.
  4. Choose your Access Mode: "Table or View" or "SQL Statement". Select "Table or View" to use the GUI to select a table or view. Select "SQL Statement" to configure a statement of your choice.
  5. Next, you need to configure fields you wish to use to perform the lookup on. Click on a column in the Available Input Columns list and drag it to the column you want to look up against in the Available Lookup Columns box.
  6. Optionally, you can select fields you would like to include in the outputs by checking the boxes for each column in the Available Lookup Columns section. You have the option to add the field to your output as a new field or to overwrite one of the fields in the input.

Full Cache

The default cache mode for the Lookup Component is Full Cache. With this mode, the Lookup Component creates a temporary SQLite cache file as soon as the first input row is detected. The cache is populated with all the rows in the Microsoft Dynamics 365 table but only the columns that are selected in the UI. Once the cache is built, each row in the input is looked up against this local cache. This mode is optimal when working with large sets of data that require many lookups.

Partial Cache

Partial Cache mode is similar to Full Cache, but instead of pulling the entire table initially, the Lookup Component batches 100 rows from the input and issue a query to the data source in the following form and cache only those results:
SELECT ... WHERE LookupField IN ('value1', ..., 'value100')
This mode is only possible if looking up a single column. This mode can improve performance if you're working with a small set of input data where caching the entire Microsoft Dynamics 365 table is very expensive.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8509