SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016
Download 32 bitSQL Server 2005, 2008, 2008R2, 2012, 2014, 2016
Download 64 bitOverview
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
Setup
Use the parameters below to set up the component.
Parameters
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. |
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. |
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>};...
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.
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.