Dynamics CRM Destination
Overview
This SSIS Data Flow Component loads data into the Dynamics CRM service.
- Supports four entity actions: Create, Update, Delete or Upsert
- The configuration parameters are provided in the standard data flow destination component dialog.
- Provides information about records with errors as well as the GUID assigned for new records created.
- Configurable parameters for Upsert action specify how to handle multiple matches and what keys to use to identify existing records.
- Phenomenal performance: a customer used our component to migrate 40 Million records in 10.5 Hours “in a precise and timely manner.”
Demonstration
Quick Start
In this section we will show you how to set up a Dynamics CRM Destination component.
- Before you begin, configure a Dynamics CRM connection manager.
- Configure a source component that will provide the data to be written to Dynamics CRM.
- Ensure that you are on the Data Flow canvas.
- In the SSIS Toolbox, locate the Dynamics CRM Destination component and drag it onto the Data Flow canvas.
- Connect the blue arrow from the source component or the data flow transformation that you want to immediately precede the Dynamics CRM Destination component.
- Double-click on the component on the canvas.
- Once the component editor opens, select the connection manager you configured earlier from the Connection drop-down list.
- Choose the desired Action (Create, Update, Delete, or Upsert).
- Choose the Entity you will be working with.
- The options will vary depending on the Action you selected. Check the options you desire. Descriptions of the options can be found in the Parameters section of the Dynamics CRM Destination documentation.
- Click OK to close the component editor.
Congratulations! You have successfully configured the Dynamics CRM Destination component.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing Dynamics CRM connection manager.
Related Topics: Dynamics CRM Connection Manager
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. After changing the action, you must use the Refresh button in the 'Component Properties' or 'Column Mappings' tab to reload the destination entity metadata.Indicate the destination Dynamics CRM entity where the data is to be loaded.
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.
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.
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.
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. 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). 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. 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 with this option for the UpsertMatch parameter. To use the GUID as the identifier for Upsert, choose the PrimaryKey option. Select the attributes you want to use as alternate keys for the Upsert action.
Specify how to handle rows with errors. To see how to set this up, see the demo video.
Contains the unique identifier of the added, updated or deleted record. To see how to set this up, see the demo video.
FAQ
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.
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.
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.The component's Error Output is used for providing both error information and the record identifier information for new records. Check the ErrorCode column:
ErrorCode Description -1 Not an error record. >0 Error record. Check the ErrorDescription column for more details. 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.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.This issue is related to IIS and to the way the connections are managed. If there are too many web service calls in a short amount of time, this may cause server sockets exhaustion. To overcome this issue, enable ConnectionSharing property on the Dynamics CRM Connection Manager.
If you are using IIS7, there is a limit of the size of file you can upload. You have to increase maxAllowedContentLength property.
Related scripts
Samples
What's New
- Fixed: Various fixes related to the IgnoreUnchanged option (Thank you, Mihkel).
- Fixed: Dialog opening generated error when using older SSIS packages (Thank you, Senthil).
- Fixed: Component didn't handle type attributes for Customer fields correctly (Thank you, Siraj).
- Fixed: Component assumed input date/time columns without time zone information are local time and converted them to UTC. This broke backwards compatibility for existing packages.
- Fixed: Component failed when trying to set salesorder entity as fulfilled (Thank you, Marvin).
- Fixed: Component failed to insert records in stringmap entity (Thank you, Kyle).
- Fixed: Component failed to set entity state in non-US localized target server (Thank you, Angelo).
- Fixed: Component failed to resolve incident entity when using Dynamics CRM 2011 web services (Thank you, Maurice).
- New: A new parameter UpsertMatch for Upsert action with three options: PrimaryKey, AlternateKeys and DuplicateDetectionRules.
- New: Support for the new Dynamics CRM 2015 UpsertRequest when using parameter UpsertMatch=AlternateKeys.
- New: A new parameter IgnoreNullValue.
- Fixed: Component was not detecting unchanged date/time input values properly when IgnoreUnchanged parameter was set to True (Thank you, Jessie).
- Fixed: Component failed to handle quote entity state attribute setup (Thank you, Jake).
- New: A new parameter MultipleMatch for use with Upsert action. The choices are: UpdateFirst, UpdateNone, UpdateAll, Fail.
- New: A new parameter IgnoreUnchanged to avoid updating entity attributes where the input values match the current values.
- Fixed: Component failed with error "String was not recognized as a valid Boolean" when trying to process boolean attributes input data set to 1 (Thank you, Srivenkata).
- Fixed: Component didn't include owneridtype attribute for update.
- New: Component now supports CRM 2011 bulk-load API.
- Fixed: Component failed with error "The maximum message size quota for incoming messages (2097152) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element." when processing large number of columns in CRM 2011 web services (Thank you, Dave).
- New: Support for Upsert action.
- New: A new parameter DetectDuplicate to enable duplicate detection rules during create or update.
- Fixed: Component was always reporting no error code (Thank you, Eric).
- New: Component now supports adding and removing records in CampaignActivityItem and CampaignItem entities.
- Fixed: Component failed to process input columns of type DT_NTEXT.
- New: Component now supports adding and removing records in ListMember entity.
- New: Component now supports update of more entity attributes.
- New: Component now provide better error diagnostic information.
- Fixed: Failed to create entity, when only one attribute was specified.
- New: Component now provides newly created records identifier.
- New: Introduced component.
Related documentation
Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.