Dynamics CRM Destination

Overview

The Dynamics CRM Destination Component is an SSIS Data Flow Component for loading data into the Microsoft Dynamics CRM entity. The configuration parameters are provided in the standard data flow destination component dialog.

Demonstration

Demonstration Video

Setup

Use the parameters below to set up the component.

Parameters

DYNAMICS-CRM

Select an existing Dynamics CRM connection manager.

Related Topics: Dynamics CRM Connection Manager

Action

Select a destination entity action. This parameter has the options listed in the following table.

Action	Description
Create	Create new records in the destination entity.
Update	Update existing records in the destination entity. You must specify the appropriate entity record identifier (the GUID assigned by Dynamics CRM) for the update to work.
Delete	Delete an existing record from the destination entity. You must specify the appropriate entity record identifier (the GUID assigned by Dynamics CRM) for the delete to work.
Upsert (1.6)	Update existing records and insert new records in the destination entity. Selecting this action displays the dynamic parameter UpsertMatch.

Note: After changing the action, you must use the Refresh button in the 'Component Properties' or 'Column Mappings' tab to reload the destination entity metadata.

DestinationEntity

Indicate the destination Dynamics CRM entity where the data is to be loaded.

IgnoreMissingIdentifier

Specify how unrecognized identifiers are to be handled. When this parameter is set to True, the component will ignore an identifier field if it does not match any entity record in the system. The identifier field must be properly formatted in order to benefit from this parameter. The component will process the remaining fields in the input record as if the identifier field was not included. If this parameter is set to False, an identifier field that does not match any entity record in the system will cause the whole record to fail with an error. The default value is False.

Example: If the Primary Contact is being updated in an Account record and the Identifier for the Primary Contact is wrong, the rest of the Account record will still be updated if this parameter is set to True. This is shown in the demo video.

DetectDuplicate (1.5 SR-3)

This parameter is only here for backward compatibility with implementations prior to version 1.6. For implementations before version 1.6, set this to True if you want to be able to determine which of your input records are new and which already exist. For implementations using version 1.6 or later, use the Upsert Action instead. The default value is False.

BatchSize (1.6 SR-1)

Specify the number of rows to be sent as a batch. When set to 1, the component will use the regular single request API. When set to greater than 1, the component will use the new CRM 2011 bulk multi-requests API. CRM 3 and 4 systems will ignore this setting and always use single request API.

MultipleMatch (1.6 SR-2)

Specify how to handle multiple matches when using Upsert action. This parameter has the options listed in the following table.

Option	Description
UpdateFirst	Update only first match (default).
UpdateNone	Update none.
UpdateAll	Update all matches.
Fail	Fail processing.

IgnoreUnchanged (1.6 SR-2)

Specify to avoid sending fields where input data matches existing data. This parameter has the options listed in the following table.

Option	Description
True	The existing data is loaded and compared to input data. If input data is same as existing data, it is not included for update.
False	All input data is included in update (default).

IgnoreNullValue (1.6 SR-4)

Specifies how NULL values are handled. This parameter has the options listed in the following table.

Options	Description
True	The NULL values are ignored and not sent for processing.
False	The NULL values are sent for processing.

UpsertMatch (1.6 SR-4)

Specify the method used to determine which records are new and which already exist. This parameter has the options listed in the following table.

Option	Description
PrimaryKey	Specifies to determine existing records by primary key.
AlternateKeys	Specifies to determine existing records using alternate keys. Selecting this option displays the dynamic parameter UpsertAlternateKeys.
DuplicateDetectionRules	Specifies to determine existing records using the system-defined duplicate detection rules. Duplicate Detection must be enabled in Dynamics CRM and rules must be published in order for this to work. Use the Duplicate Detection Rules in Dynamics CRM to indicate the field(s) to be used to match records. The entity record identifier (the GUID assigned by Dynamics CRM) may not be used as the identifier when using Upsert.

UpsertAlternateKeys (1.6 SR-4)

Select the attributes you want to use as alternate keys for the Upsert action.

ErrorRowDisposition (Dynamics CRM Destination Input)

Specify how to handle rows with errors. To see how to set this up, see the demo video.

Id (Dynamics CRM Destination Error Output) (1.5 SR-1)

Contains the unique identifier of the added, updated or deleted record. To see how to set this up, see the demo video.

How to modify a destination entity dynamically at runtime

The destination entity is available in the data flow properties list. Follow these steps to set up an expression:

• Right-click on the data flow canvas and select the Properties menu.
• Scroll down and find the property named [Dynamics CRM Destination].[DestinationEntity]. This is the property containing the destination entity.
• Scroll down and find the Expressions property. Set up an expression to modify the destination table dynamically.

How to get the identifier of a newly created entity record

The identifier of the created entity record is provided in the component's error output, in the Id column. Connect the error output to a destination where you want to store the identifiers. See the demo video for details about setting up a destination in which to write the new IDs.

How to set up an activity party list attribute

The activity party list can be set up either with the party's email or using a party-unique identifier. Multiple party elements are separated with a semicolon (;):

• To use email addresses to identify the parties: email1;email2;...
• To use the GUIDs assigned to the records by Dynamics CRM: <entity name>:{<guid1>};<entity name>:{<guid2>};...

See the demo video for details about setting up an activity party list.

How to find which records contain errors

The component's Error Output is used for providing both error information and the record identifier information for new records. Check the ErrorCode column:

You can set up the standard Conditional Split component to filter non-error records.

See the demo video for details about setting up a destination in which to write errors.

How to upload attachment

The attachments are stored in the annotation entity. Use the standard Import Column transformation to load the file content into memory and then direct into documentbody attribute. See the demo video for details.

Related scripts

• Map Replace Transformation script - Perform find-and-replace transformation in specified input columns using dictionary object.
• Dynamics CRM Association Destination script - Create associations between entities (assign roles to users).
• Dynamics CRM Relationship Destination script - Relate records between two entities.
• Dynamics CRM Route Destination script - Route records to queue.
• Dynamics CRM User Access Destination script - Establish user access permissions to entities.
• Dynamics CRM Bulk Delete Task script - Bulk deletion task.
• Dynamics CRM Execute Workflow Destination script - Executes workflow.
• Dynamics CRM Incident State Destination script - Updates incident entity state.
• Dynamics CRM Opportunity State Destination script - Updates opportunity entity state.
• Dynamics CRM Team Members Destination script - Establishes team members relationship.

Samples

• CRM 2011 Data Import / Export using Cozy Roc SSIS library by Mani Ramachandran.
• Importing Email into MS CRM 2011 via SSIS by Joe Peyton.
• The Great Migration: 40 Million Records Migrated in 10.5 Hours by John Klemetsrud.