The CRM Lookup Component allows you to look up data from Microsoft CRM, versions 4 and 2011, into a data-flow. The relationships in Microsoft CRM can be readily navigated.

To use the CRM Lookup Component, simply drag an instance from the toolbox, connect the predecessor component and then double click to configure.  The following form is presented :

 

You need to enter the root URL of your CRM server, and your configured organization name.  You can also specify a user id and password - in the absence of these your Windows account (and at runtime the account under which SSIS runs) will be used.  Finally you can also specify a communications timeout in seconds, and a join mode for navigating relationships.

The second tab allows you to select which entities and attributes to return or query.  All entities are listed in a tree control, and can be expanded to select individual attributes that you want to capture as columns or filter upon in the data-flow.

 

We'll select the ContactId and atr_ECAN attributes of the Contact entity (where atr_ECAN is a custom attribute in this system).  Note that you can also navigate relationships in this tree - see the CRM Source Component for an example.

The Data Types / Filtering tab then allows us to configure the column name, data type and any filter conditions for the selected fields.

 

For each selected attribute that you want to return into a column, you should confirm and edit as necessary the column name and data type by clicking the 'Edit Name / Data Type' button. For each attribute that you want to filter upon, you should select a condition and an association comparison value from the dropdowns and then click the 'Update' button. You can select variables and columns, or you can also type literal values. All attributes will be returned into the data-flow except those matching on Equals conditions, since by definition you will already know the value of these attributes.  So here we have specifed that we want to match atr_ECAN against the current value of the MAIN_CONTACT column in the data-flow, and return the associated ContactId as a column named ContactId.

The Lookup tab allows us to specify lookup behaviour where there are no or multiple matching records, or where returned data is truncated.

 

Finally, the Condition tab allows us to 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 [MAIN_CONTACT] != 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 :