The CRM Update Component allows you to update data in Microsoft CRM, versions 4 and 2011, from a Microsoft SQL Server Integration Services data-flow. Like the CRM Source Component, it allows you to intuitively map from tabular data into the CRM entity and attribute model.

To use the CRM Update Component, simply drag an instance from the toolbox, link 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 update mode. The update mode specifies how matching records in Microsoft CRM are processed. Delete means matching records are deleted. Updated means matching records are updated. Insert means records are inserted - this will cause a failure if the record already exists. Upsert is an intelligent combination of Update and Insert. The (all children) specification means that any child records relating to configured relationships that are not present in the SSIS data will be deleted.  For any mode apart from Insert to be meaningful, you must bind at least one attribute by Equals or condition.

The second tab allows you to select which entities and attributes to update or filter upon.  All entities are listed in a tree control, and can be expanded to select individual attributes that you want to update or filter upon.



By expanding the Relationships node you can also navigate the relationships.  Here we expand the relationship from Contact to Account, thus getting the list of contacts associated with each account.

 

Here we select FirstName and LastName. BlueSSIS intelligently converts these relationships into a joined set of columns, and infers updates appropriately.

 

The Data Bindings tab allows you to select the binding for each selected attribute. Attributes can be bound to constant values, column names (enclosed in [square brackets]) or variables (prefixed with @). You can drop down the Binds To combo box to get a list of available variables or columns. You also need to select the binding type, which can be setting the value or the attribute or record matching based on a condition on the value of the attribute. The image below shows the full range of binding options. Note that for In and Between conditions, the associated value must be an array or a comma separated list.

 

The Visual Binder tab allows you to set this same binding information via a graphical, drag and drop interface. You simply drag from the available values on the left onto the attributes on the right. Constants can be added by simply typing them into the empty textbox at the bottom, and can be similarly dragged onto attributes.

 

New : Error routing

You can now also route failed updates to the error output, using the new Error Handling tab - this makes error handling in some scenarios much simpler. The error output contains an additional column called __ErrorDescription containing the full error content.

 

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 :