COZYROC Dynamics CRM components, part of COZYROC SSIS+ suite since 2010, are third-party plug-ins for Microsoft SSIS that make it easy to integrate or migrate data to and from Microsoft Dynamics CRM. They will preserve the integrity of your data by always using the recommended Microsoft web services API.
Reusable Scripts, along with the Source and Destination components, provide support for all the capabilities you may desire for integration and migration of your Dynamics CRM data. The Source and Destination components are purely for actions related to Dynamics CRM entities, while the scripts provide functionality related to Dynamics CRM, but not directly related to actions with entities.
You can use any application, service or database supported by SSIS or COZYROC’s toolkit (SSIS+) as the source or destination for Dynamics CRM. SSIS+ is easy to use and follows the same guidelines and principles used by the standard out-of-the-box SSIS components.
For migrations from on-premise Dynamics CRM to on-the-cloud Dynamics CRM, you may use COZYROC’s Data Flow Task Plus (see side-bar) along with the Dynamics CRM Source and Destination components for an elegant and time-saving migration solution. Source and destination columns get mapped dynamically at runtime so one package can handle the migration of data for every entity.
See the Source and Destination documentation for some short and informative demonstration videos which provide step-by-step instructions on how to get started.
The Dynamics CRM integration package consists of four parts:
- Dynamics CRM Connection Manager
- Stores the credentials and functionality for connecting to an on premise or in-the-cloud Dynamics CRM server.
- Well documented API for use by user-defined scripts.
- Independent from the hard-coded Microsoft Dynamics CRM SDK.
- Dynamics CRM Source component
- Retrieves data from a Dynamics CRM entity.
- Input type can be either an entity or a FetchXml statement.
- The source can be dynamically modified at runtime using an SSIS expression.
- Dynamics CRM Destination component
- Inserts or updates data in Dynamics CRM entity.
- Supports four entity actions: Create, Update, Delete or Upsert
- 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.”
- Reusable scripts
- Unique technology which allows user-defined reusable scripts for additional Dynamics CRM integration scenarios.
Technical Specifications
- Support Dynamics 365, Premise, Live, Hosted deployments.
- Support Dynamics CRM 2016, 2015, 2013, 2011, 4.0 and 3.0 systems.
The COZYROC SSIS+ library also includes the powerful and elegant Data Flow Task Plus, not found in any other competitive product on the market. Data Flow Task Plus allows creation of dynamic data flows at runtime and can be used with COZYROC's Dynamics CRM adapters.
By using this task, the mapping of columns from the source to the destination is done dynamically at runtime. This means you can create a single process to migrate the data from every Dynamics CRM entity from on-premise to on-the-cloud.
In addition, you can create integration processes where the addition or deletion of Dynamics CRM entity attributes doesn't necessitate modification of existing SSIS packages. This powerful feature requires no programming skills to use and greatly simplifies your maintenance efforts.
Microsoft Dataverse Connection
Step 1. Right click Visual Studio to launch using "Run As Administrator".
Step 2. Right click on "Connection Managers" tab and select "New Connection..."
Step 3. In "Add SSIS Connection Manager" scroll down and select "REST connection manager" and click "Add..."
Step 4. From Configuration combo box select "Microsoft Dataverse".
Step 5. Specify your instance in the Server parameter. The parameter should appear like https://[your organization instance name].crm.dynamics.com/api/data/v9.0 for an instance located in North America.
Step 6. Click New button to create a new token file.
Step 7. During the token generation process you will see that client_id has a default client_id. The default client_id is an application registered by COZYROC and validated by Microsoft. The customer now has a choice for one of the following options below. Most customer's are choosing option #2 as Microsoft has not made it clear how the customer is to give the permissions for the 1st option.
1. The customer's Administrator will need to give the COZYROC application consent and then and only then can the customer proceed in the token generation wizard to give login consent to COZYROC's application.
2. Setup your own client_id, instead of using the one proviced by COZYROC. This step needs to be done to create your own client_id to connect and to be used during the token generation. The following KB article will help you setup your own client_id:
How to setup your client id to connect with COZYROC Microsoft Dataverse Connector
If you have issues generating the token, set the token wizard to use a different browser engine and be sure to launch Visual Studio using Run as Admin.
Step 8. Enter your credentials for Microsoft Dataverse and select Next.
Step 9. Create and Save your password protected Microsoft Dataverse Service token file. Click OK.
Step 10. Click "Test Connection" to test your connection.
In this guide, we will show how to read data from Microsoft Common Data Service resource using the COZYROC REST Source component.
Step 1. Select existing "Data Flow task" or create new "Data Flow Task".
Step 2. Open "SSIS Toolbox" and drag and drop COZYROC "REST Source" component.
Step 3. Double click on "REST Source" component and select the Microsoft Common Data Service connection from the combo. Select from available Common Data Service resources listed in the second combo below.
Optionally you can include extra parameters in the grid below if needed.
Step 4. Click on "Columns tab" and select the resource columns you want to read.
Step 5. Click Preview to make sure all necessary resource columns are included.
Bravo! You have now learned how to read data from Microsoft Common Data Service service.
In this guide, we will show how to write data to Microsoft Common Data Service resource using the COZYROC REST Destination component.
Step 1. Select existing "Data Flow task" or create new "Data Flow Task".
Step 2. Open "SSIS Toolbox" and drag and drop COZYROC "REST Destination" component.
Step 3. Double click on "REST Destination" component and select the Microsoft Common Data Service connection from the combo. Also select resource action and Common Data Service resource you want to work on.
Step 4. Click on "Mapping" tab and map the input columns for the process you are configuring.
Done. You have now learned how to write data into Microsoft Common Data Service resource.
In this guide, we will show you how to create a connection to Microsoft Common Data service using COZYROC's REST Connection Manager.
Step 1. Run "Visual Studio".
Step 2. Right click on "Connection Managers" tab and select "New Connection..."
Step 3. In "Add SSIS Connection Manager" scroll down and select "REST connection manager" and click "Add..."
Step 4. From Configuration combo box select "Microsoft Common Data Service".
Step 5. Specify your instance in the Server parameter. The parameter should appear like https://[your organization instance name].crm.dynamics.com/api/data/v9.0 for an instance located in North America.
Step 6. Click New button to create a new token file.
Step 7. If you don't have issues generating the token, you may press the Next button. If you have issues generating the token, set the token wizard to masquerade as a different browser.
Step 8. Enter your credentials for Microsoft Common Data Service.
Step 9. Consent COZYROC application can access your data on your behalf and click on Accept button.
Step 10. Create and Save your password protected Microsoft Common Data Service token file. Click OK.
Step 11. Click "Test Connection" to test your connection.
Congratulations! You have now established a connection to your Microsoft Common Data Service instance.
Dynamics CRM Source
In this section we will show you how to set up a Dynamics CRM Source component.
- Before you begin, configure a Dynamics CRM connection manager.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- In the SSIS Toolbox, locate the Dynamics CRM Source component and drag it onto the Data Flow canvas.
- 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.
- For Input, either select Entity or Statement.
- If you selected Entity, then select the entity you want to work with from the drop-down list.
- If you selected Statement, then compose a FetchXML statement to retrieve the entity and records you want from the source.
- Click OK to close the component editor.
Congratulations! You have successfully configured the Dynamics CRM Source component.
Dynamics CRM Destination
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.
Dynamics CRM Connection
In this section we will show you how to set up a Dynamics CRM Connection Manager to access an on-premise installation of Dynamics CRM. The same method can be used to set up a connection manager for Dynamics CRM in the cloud.
- Right-click on the Connection Manager canvas.
- Select New Connection.
- When the list of connection managers appears, expand the Type column, if necessary, so you can see the complete names of the connection managers.
- Scroll down to find Dynamics CRM and select it.
- Once the connection manager dialog opens, select your Edition from the drop-down list (CRM 2011+, CRM 4.0 or CRM 3.0).
- Select your type of Deployment from the drop-down list (Premise, Live, or Hosted).
- Enter the URL or IP address for the Server.
- Enter your User Name.
- Enter your Password.
- Choose your Organization.
- Click on Test Connection.
- When you see Test connection succeeded, click OK.
- Click OK to close the connection manager dialog.
Congratulations! You have successfully configured a Dynamics CRM connection manager.
Overview
Dynamics CRM Connection Manager is SSIS Connection Manager for establishing connection to an on premise or in-the-cloud Dynamics CRM server.
- Well documented API for use by user-defined scripts.
- Independent from the hard-coded Microsoft Dynamics CRM SDK.
Quick Start
In this section we will show you how to set up a Dynamics CRM Connection Manager to access an on-premise installation of Dynamics CRM. The same method can be used to set up a connection manager for Dynamics CRM in the cloud.
- Right-click on the Connection Manager canvas.
- Select New Connection.
- When the list of connection managers appears, expand the Type column, if necessary, so you can see the complete names of the connection managers.
- Scroll down to find Dynamics CRM and select it.
- Once the connection manager dialog opens, select your Edition from the drop-down list (CRM 2011+, CRM 4.0 or CRM 3.0).
- Select your type of Deployment from the drop-down list (Premise, Live, or Hosted).
- Enter the URL or IP address for the Server.
- Enter your User Name.
- Enter your Password.
- Choose your Organization.
- Click on Test Connection.
- When you see Test connection succeeded, click OK.
- Click OK to close the connection manager dialog.
Congratulations! You have successfully configured a Dynamics CRM connection manager.
Parameters
Server
Use the Server page of the Dynamics CRM Connection Manager dialog to specify properties for connecting to Dynamics CRM service.
- Test Connection
- Confirm connection manager configuration by clicking Test Connection.
Specify edition. This property has the options listed in the following table.
Value Description CRM 2011 Connect to CRM 2011 and above web services. Requires .NET 3.0 and up. CRM 4.0 Connect to CRM 4.0 web services. CRM 3.0 Connect to Dynamics CRM 3.0. Specify the deployment type. This property has the options listed in the following table for CRM 2011 edition.
Note: When using Dynamics CRM Online, it is a good idea to always use the latest version of SSIS+, because there are fixes made to keep up with the latest updates by Microsoft.Value Description Office 365 Connect to Office 365 deployed Dynamics CRM. Premise Connect to on premise Dynamics CRM. Live Connect to Dynamics CRM Live service. This property has the options listed in the following table for CRM 4.0 edition.
Value Description Premise Connect to on-premise Dynamics CRM server. Live Connect to Dynamics CRM Live service. Hosted Connect to hosted Dynamics CRM. Specify the name or IP address of the Dynamics CRM service. Microsoft-hosted Dynamics CRM uses different address for different regions. The addresses for each region and web service type are listed in the following tables.
-
Office 365 CRM 2011
Value Description disco.crm.dynamics.com North America (default) disco.crm4.dynamics.com EMEA disco.crm5.dynamics.com APAC -
Live CRM 2011
Value Description dev.crm.dynamics.com North America (default) dev.crm4.dynamics.com EMEA dev.crm5.dynamics.com APAC -
Live CRM 4.0
Value Description crm.dynamics.com North America (default) crm4.dynamics.com EMEA crm5.dynamics.com APAC
-
Specify user name to access Dynamics CRM.
Specify password to access Dynamics CRM.
Select organization name.
Not available in CRM 3.0Specify the number of seconds before timing out session connect. The default value of this property is 100 seconds.
Specify if all clients should use the same connection. This property has the options listed in the following table.
Value Description True All clients use same connection (default). False Every client use different connection. Specify caller identifier for user impersonation.
Proxy
Use the Proxy page of the Dynamics CRM Connection Manager dialog to specify properties if your server is behind firewall.
Specify the name or IP address of the proxy server. If not specified, proxy server is not used.
Specify the port number on the proxy server to use for the connection.
Specify user name to access the proxy server.
Specify password to access the proxy server.
Knowledge Base
- Error Message: Policy node not found
- Error Message: FCB 'EnableRegionalDisco' is disabled
- Where can I find the documentation for the Dynamics CRM Connection?
- Error Message: An error occurred when processing the security tokens in the message:You are using Ws-Trust authentication which has been deprecated and no longer supported in your environment. Please use OAuth2.0 authentication
- When Dynamics CRM is setup in IFD (Internet Facing Deployment) authentication mode I get error: "The request failed with HTTP status 401: Unauthorized. (System.Web.Services)".
What's New
- New: A new OrganizationUrl parameter for setting organization service address. When specified, the discovery service is not used.
- Fixed: Connection exhaustion because of resource leak in the system code, causing unexplained process slowdown (Thank you, Jake).
- New: Support for Multi-Select Picklist attributes.
- New: Setup a more appropriate DT_DBDATE column data type for attributes with DateTimeFormat.DateOnly setting.
- New: Setup includes the unique name of the selected organization.
- New: Access Control Service (ACS) is no longer used to connect to Dynamics 365/CRM Online.
- Fixed: Type attributes for Customer fields were not handled correctly (Thank you, Siraj).
- Fixed: Failed with error "http://:80 end point not found" when trying to connect to Dynamics CRM 2015 Update 1 and above.
- Fixed: Failed with error "ID3242: The security token could not be authenticated or authorized." when processing for more than 8 hours (Thank you, Tamanna).
- Fixed: Failed with error "Security Support Provider Interface (SSPI) authentication failed." when trying to connect to CRM 2011 on premise server using Windows authentication.
- New: A new CallerId parameter for setting user impersonation.
- New: Support for CRM 2011 web services ActiveDirectory authentication.
- New: Support for Office 365 deployed systems with Federated (delegated) security.
- New: Support for CRM 2011 web services.
- Fixed: Not able to connect to hosted CRM service (Thank you, Joao).
- New: Support for CRM Live deployment in EMEA and Asia.
- Introduced connection.
Related documentation
Overview
This SSIS Data Flow Component retrieves data from the Dynamics CRM service.
- The Input type can be either an entity or a FetchXml statement.
- The configuration parameters are provided in the standard Data Flow Component dialogs.
- The source can be dynamically modified at runtime using an SSIS expression.
Demonstration
Quick Start
In this section we will show you how to set up a Dynamics CRM Source component.
- Before you begin, configure a Dynamics CRM connection manager.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- In the SSIS Toolbox, locate the Dynamics CRM Source component and drag it onto the Data Flow canvas.
- 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.
- For Input, either select Entity or Statement.
- If you selected Entity, then select the entity you want to work with from the drop-down list.
- If you selected Statement, then compose a FetchXML statement to retrieve the entity and records you want from the source.
- Click OK to close the component editor.
Congratulations! You have successfully configured the Dynamics CRM Source component.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing Dynamics CRM connection manager.
Related Topics: Dynamics CRM Connection Manager
Specify the number of rows to be retrieved as a batch. The maximum is 500.
Specify the input type. This parameter has the options listed in the following table.
Value Description Entity The input type is a Dynamics CRM entity. Selecting this value displays the dynamic parameter Entity. FetchXML The input type is a FetchXML query. Selecting this value displays the dynamic parameter FetchXML. After changing the input type, you must use the Refresh command in the 'Column Mappings' tab to reload the source entity metadata.Select the Dynamics CRM data entity.
Specify the FetchXML statement. Select
to design your query in the standard Advanced Find dialog.Advanced Find is not available in CRM 3.0
Knowledge Base
- Where can I find the documentation for the Dynamics CRM Source?
- Error Message:Combined execution time of incoming requests exceeded limit of 1200000 milliseconds over time window of 300 seconds.
- When setting sort/order in FetchXML on Dynamics CRM Source, the component returns only the number of rows set in batch size
- Error Message:Combined execution time of incoming requests exceeded limit of 1200000 milliseconds over time window of 300 seconds.
- When I try to select entity in Dynamics CRM Source component I get error: "No connection could be made because the target machine actively refused it". Dynamics CRM Connection Manager 'Test Connection' command works fine.
Related scripts
Samples
What's New
- Fixed: Component truncated timestamp from Date-only formatted attributes.
- Fixed: Component returned date-only attributes incorrectly in UTC (Thank you, Maurizio).
- Fixed: Component failed with error "The top attribute can't be specified with paging attribute page" when FetchXML
topelement was used (Thank you, Cosmin).
- Fixed: Component incorrectly converted input UTC datetime columns to local time. This broke backwards compatibility for existing packages (Thank you, Saritha).
- New: Component setup dialog now includes data Preview tab.
- Fixed: Component didn't handle type attributes for Customer fields correctly (Thank you, Siraj).
- Fixed: Component failed when specified FetchXml statement contained apostrophe (') symbol (Thank you, Andrew).
- Fixed: Component created duplicate column names when using FetchXml statement containing link-entity element with reference to the same entity with two different aliases (Thank you, Andrew).
- Fixed: Component failed with error "An error occurred while parsing EntityName..." when input data contained invalid activity party email address (Thank you, Nicholas).
- Fixed: Component failed to read BooleanManagedProperty attributes (Thank you, John).
- Fixed: Component was inefficient when retrieving from activities in system with millions of activity party records (Thank you, Allen).
- Fixed: Component did not handle aliased values in CRM 2011 (Thank you, Yatik).
- Fixed: Component now handles better FetchXML statements containing linked entities.
- Fixed: Failed when certain FetchXML statements were specified (Thank you, Jamal).
- New: Component can now retrieve data from StringMap and ListMember entities.
- New: Component now provide better error diagnostic information.
- Fixed: Fixes to support Dynamics CRM 2011.
- Introduced component.
Related documentation
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 Component dialogs.
- 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.
Knowledge Base
- Where can I find the documentation for the Dynamics CRM Destination?
- Why the field Statuscode in Dynamics CRM Entity: Account doesn't get updated?
- Error Message:Combined execution time of incoming requests exceeded limit of 1200000 milliseconds over time window of 300 seconds.
- Error Message:Combined execution time of incoming requests exceeded limit of 1200000 milliseconds over time window of 300 seconds.
- How to get the identifier of a newly created entity record
Related scripts
Samples
What's New
- Fixed: Component was not detecting boolean input values properly because of a different case when IgnoreUnchanged parameter was set to True.
- 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
Overview
These public interfaces are used in Dynamics CRM components. They are provided for implementing advanced functionality in your scripts. In order for you to use these interfaces in your project, you have to reference CozyRoc.SSISPlus.dll and CozyRoc.Dynamics.dll libraries. Also include CozyRoc.Dynamics.Crm, CozyRoc.Dynamics.Crm.Service, CozyRoc.Dynamics.Crm.MetadataService, CozyRoc.Dynamics.Crm.DiscoveryService namespaces.
IConnection / ICrmConnection
When you call Dynamics CRM Connection Manager AcquireConnection method, it returns object implementing IConnection interface. This is the interface used for interaction with Dynamics CRM service.
Methods
Connects to Dynamics CRM service.
Closes the connection to Dynamics CRM service. Use always after you finish working with the service.
Returns true if connected to the service.
Get specific Dynamics CRM web service. These are the method parameters:
- service (int) - service type. It can be one of the options listed in the following table.
Value Result Service 0 - CRM 2011 - returns service class from CozyRoc.Dynamics.Crm2011.OrganizationService namespace
- CRM 4.0 - returns CrmService class from CozyRoc.Dynamics.Crm.Service namespace.
- CRM 3.0 - returns service class from CozyRoc.Dynamics.Crm3.Service namespace.
1 - CRM 2011 - Not available.
- CRM 4.0 - returns MetadataService class from CozyRoc.Dynamics.Crm.MetadataService namespace.
- CRM 3.0 - returns service class from CozyRoc.Dynamics.Crm3.MetadataService namespace.
2 - CRM 2011 - returns service class from CozyRoc.Dynamics.Crm2011.DiscoveryService namespace.
- CRM 4.0 - returns CrmDiscoveryService class from CozyRoc.Dynamics.Crm.DiscoveryService namespace.
- CRM 3.0 - Not available.
Gets organizations list.
Gets entities list with specific privilege. These are the method parameters:
- privilege (int) - entity privilege. It can be one of the options listed in the following table.
Value Description 0 Privilege unspecified. All entities will be returned. 1 Return entities, which permit record creation. 2 Return entities, which can be read. 3 Return entities, which can be updated. 4 Return entities, which permit record removal.
Query Dynamics CRM service. The method returns XML string with the result of the query. These are the method parameters:
- fetchXML (string) FetchXML query statement.
DEPRECATED: Use IEntity.GetAttributes method instead.
Get entity attributes. The method returns array of IAttribute objects. These are the method parameters:
- entity (string) - entity name.
Get attributes specified in query. The method returns array of IAttribute objects. These are the method parameters:
- fetchXML (string) - FetchXML query statement.
DEPRECATED: Use GetEntity method instead.
Construct object, which will be used to manipulate entity. The method returns ICrmEntity object. These are the method parameters:
- name (string) - entity name.
DEPRECATED: Use IEntity interface instead.
Creates record in entity. The method returns string with identifier of the created record. These are the method parameters:
- entity (ICrmEntity) - object with create data.
- ignoreMissingId - specify to ignore attributes referencing missing data.
- detectDuplicate - specify to enable duplicate detection rule.
DEPRECATED: Use IEntity interface instead.
Updates existing record in entity. These are the method parameters:
- entity (ICrmEntity) - object with update data.
- ignoreMissingId - specify to ignore attributes referencing missing data.
- detectDuplicate - specify to enable duplicate detection rule.
DEPRECATED: Use IEntity interface instead.
Deletes record in entity. These are the method parameters:
- entity (ICrmEntity) - object with delete data.
DEPRECATED: Use IEntity interface instead.
Update and insert a record in destination entity. These are the method parameters:
- entity (ICrmEntity) - object with update data.
- ignoreMissingId - specify to ignore attributes referencing missing data.
Get object used to manage Dynamics CRM entity. The method returns IEntity object. These are the method parameters:
- name (string) - entity name.
Properties
Specify deployment type. This property has the options listed in the following table.
| Value | Description |
|---|---|
| 0 | Connect to on-premise Dynamics CRM server. |
| 1 | Connect to Dynamics CRM Live service. |
| 2 | Connect to hosted Dynamics CRM. |
| 3 | Connect to Dynamics CRM 3.0 system. |
| 4 | Connect to Dynamics CRM 2011 web services 1.6. |
Specify the name or IP address of the Dynamics CRM server.
Specify user name to access the Dynamics CRM service.
Specify user password to access the Dynamics CRM service.
Specify organization name. Note: Not available in CRM 3.0
Specify the number of seconds before timing out session connect. The default value of this property is 100 seconds.
Specify if all clients should use the same connection. This property has the options listed in the following table.
| Value | Description |
|---|---|
| True | All clients use same connection (default). |
| False | Every client uses different connection. |
Specify the name or IP address of the proxy server.
Specify the port number on the proxy server to use for the connection.
Specify user name to access the proxy server.
Specify password to access the proxy server.
IAttribute / ICrmAttribute
This interface is used to retrieve entity attributes information.
Properties
Returns attribute name.
Returns true if attribute can be read.
Returns true if attribute can be specified for record creation.
Returns true if attribute can be specified for record update.
Returns true if attribute is primary key.
Returns SSIS attribute type.
Returns attribute entity reference if attribute is lookup.
Returns internal AttributeMetadata object.
Returns attribute type. It can be one of the options listed in the following table.
CRM 3.0, CRM 4.0
| Value | Description |
|---|---|
| 0 | Boolean |
| 1 | Customer |
| 2 | DateTime |
| 3 | Decimal |
| 4 | Float |
| 5 | Integer |
| 6 | Internal |
| 7 | Lookup |
| 8 | Memo |
| 9 | Money |
| 10 | Owner |
| 11 | PartyList |
| 12 | Picklist |
| 13 | PrimaryKey |
| 14 | State |
| 15 | Status |
| 16 | String |
| 17 | UniqueIdentifier |
| 18 | Virtual |
| 19 | CalendarRules |
CRM 2011
| Value | Description |
|---|---|
| 0 | Boolean |
| 1 | Customer |
| 2 | DateTime |
| 3 | Decimal |
| 4 | Double |
| 5 | Integer |
| 6 | Lookup |
| 7 | Memo |
| 8 | Money |
| 9 | Owner |
| 10 | PartyList |
| 11 | Picklist |
| 12 | State |
| 13 | Status |
| 14 | String |
| 15 | UniqueIdentifier |
| 16 | CalendarRules |
| 17 | Virtual |
| 18 | BigInt |
| 19 | ManagedProperty |
| 20 | EntityName |
Returns attribute length.
Returns attribute precision.
Returns attribute scale.
ICrmEntity
This interface is used to manipulate entity record.
Methods
Adds attribute. The method returns the index of the newly inserted attribute. These are the method parameters:
- name (string) - attribute name.
- attributeType (int) - attribute type. For valid values check CrmType property in IAttribute interface.
- reference (string) - entity reference. Only used when dealing with lookup attributes.
Return count of attributes.
Sets attribute value. These are the method parameters:
- index (int) - attribute index.
- value (object) - attribute value.
Returns attribute value. These are the method parameters:
- index (int) - attribute index.
Constructs DynamicEntity object.
Properties
Returns the entity name this object is associated with.
IEntity / ICrmEntity2 1.6 SR-1
This interface is used to manipulate entity record.
Methods
Get entity attributes. The method returns array of IAttribute objects.
Constructs new Dynamics CRM entity data instance.
Sets attribute value in entity instance. These are the method parameters:
- entity (object) - entity instance.
- name (string) - attribute name.
- value (object) - attribute value.
Returns attribute value from entity instance. These are the method parameters:
- entity (object) - entity instance.
- name (string) - attribute name.
Creates records in entity. The method returns array of IStatus objects for each input object. These are the method parameters:
- list (object array) - array of Dynamics CRM entities to create.
- ignoreMissingId - specify to ignore attributes referencing missing data.
- detectDuplicate - specify to enable duplicate detection rule.
Updates existing records in entity. The method returns array of IStatus objects for each input object. These are the method parameters:
- list (object array) - array of Dynamics CRM entities with update data.
- ignoreMissingId - specify to ignore attributes referencing missing data.
- detectDuplicate - specify to enable duplicate detection rule.
Create records and update existing records in entity. The method returns array of IStatus objects for each input object. These are the method parameters:
- list (object array) - array of Dynamics CRM entities with create and update data.
- ignoreMissingId - specify to ignore attributes referencing missing data.
Delete existing records in entity. The method returns array of IStatus objects for each input object. These are the method parameters:
- list (object array) - array of Dynamics CRM entities to delete.
IStatus / ICrmStatus 1.6 SR-1
This interface is used for reporting back status.
Properties
Returns the Dynamics CRM entity identifier.
Returns the Dynamics CRM error code. If zero, there is no error.
Returns the Dynamics CRM status message.
Knowledge Base
Related documentation
Dynamics CRM Connection
- New: A new OrganizationUrl parameter for setting organization service address. When specified, the discovery service is not used.
- Fixed: Connection exhaustion because of resource leak in the system code, causing unexplained process slowdown (Thank you, Jake).
- New: Support for Multi-Select Picklist attributes.
- New: Setup a more appropriate DT_DBDATE column data type for attributes with DateTimeFormat.DateOnly setting.
- New: Setup includes the unique name of the selected organization.
- New: Access Control Service (ACS) is no longer used to connect to Dynamics 365/CRM Online.
- Fixed: Type attributes for Customer fields were not handled correctly (Thank you, Siraj).
- Fixed: Failed with error "http://:80 end point not found" when trying to connect to Dynamics CRM 2015 Update 1 and above.
- Fixed: Failed with error "ID3242: The security token could not be authenticated or authorized." when processing for more than 8 hours (Thank you, Tamanna).
- Fixed: Failed with error "Security Support Provider Interface (SSPI) authentication failed." when trying to connect to CRM 2011 on premise server using Windows authentication.
- New: A new CallerId parameter for setting user impersonation.
- New: Support for CRM 2011 web services ActiveDirectory authentication.
- New: Support for Office 365 deployed systems with Federated (delegated) security.
- New: Support for CRM 2011 web services.
- Fixed: Not able to connect to hosted CRM service (Thank you, Joao).
- New: Support for CRM Live deployment in EMEA and Asia.
- Introduced connection.
Dynamics CRM Destination
- Fixed: Component was not detecting boolean input values properly because of a different case when IgnoreUnchanged parameter was set to True.
- 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.
Dynamics CRM Source
- Fixed: Component truncated timestamp from Date-only formatted attributes.
- Fixed: Component returned date-only attributes incorrectly in UTC (Thank you, Maurizio).
- Fixed: Component failed with error "The top attribute can't be specified with paging attribute page" when FetchXML
topelement was used (Thank you, Cosmin).
- Fixed: Component incorrectly converted input UTC datetime columns to local time. This broke backwards compatibility for existing packages (Thank you, Saritha).
- New: Component setup dialog now includes data Preview tab.
- Fixed: Component didn't handle type attributes for Customer fields correctly (Thank you, Siraj).
- Fixed: Component failed when specified FetchXml statement contained apostrophe (') symbol (Thank you, Andrew).
- Fixed: Component created duplicate column names when using FetchXml statement containing link-entity element with reference to the same entity with two different aliases (Thank you, Andrew).
- Fixed: Component failed with error "An error occurred while parsing EntityName..." when input data contained invalid activity party email address (Thank you, Nicholas).
- Fixed: Component failed to read BooleanManagedProperty attributes (Thank you, John).
- Fixed: Component was inefficient when retrieving from activities in system with millions of activity party records (Thank you, Allen).
- Fixed: Component did not handle aliased values in CRM 2011 (Thank you, Yatik).
- Fixed: Component now handles better FetchXML statements containing linked entities.
- Fixed: Failed when certain FetchXML statements were specified (Thank you, Jamal).
- New: Component can now retrieve data from StringMap and ListMember entities.
- New: Component now provide better error diagnostic information.
- Fixed: Fixes to support Dynamics CRM 2011.
- Introduced component.
Microsoft Dataverse Connection
- New: Defined Service authentication.
- Fixed: Field's metadata was not updated after FetchXml parameter modification (Thank you, Paul).
- New: Implemented to create OAuth2 version 2.0 access tokens.
- New: Support for FetchXml data retrieve.
- New: Support for 'continue-on-error' data processing.
- New: Support for Update/Upsert with alternate keys.
- New: Support for Upsert using 'Duplicate Detection Rules' definition.
- New: A new action parameter IgnoreNullValue to ignore NULL input values.
- New: A new action parameter IgnoreMissingIdentifier to ignore missing lookup identifiers.
- New: A new action parameter IgnoreUnchanged to avoid making updates where the input data is same as current.
- New: 'listmembers' resource for list members maintenance.
- Fixed: Corrected code for creating and updating lookup fields (Thank you, Russel).
- Fixed: Corrected code for creating and updating date fields (Thank you, Vadim).
- Fixed: Child-entities not available to update (Thank you, Ginger).
- New: Introduced connection.
Knowledge Base
- Error Message: Metadata contains a reference that cannot be resolved: ''
- Why the field Statuscode in Dynamics CRM Entity: Account doesn't get updated?
- Error Message:Combined execution time of incoming requests exceeded limit of 1200000 milliseconds over time window of 300 seconds.
- Error Message:Combined execution time of incoming requests exceeded limit of 1200000 milliseconds over time window of 300 seconds.
- Error Message: Policy node not found
We started using COZYROC SSIS Tools back in 2011 when we were evaluating multiple CRM systems to replace our existing CRM system. COZYROC tools made it a breeze to move data back and forth between our SQL Server databases and Microsoft Dynamics CRM as well as Salesforce. COZYROC enabled us to rapidly transfer data among various systems for our CRM evaluation without having to write custom code to load and read data. COZYROC SSIS Tools made it easy for us to get Salesforce rolled out to new teams over the years because we were able to effortlessly transfer data from other CRM systems, MS Excel, and csv files to Salesforce.
Jamal HashimCareer Builder
MetaBank finds that COZYROC’s tools provide easily configurable tools that allow us to perform more complex operations without additional coding. We have been able to implement these components across the entire team to allow for consistent use. The components provide flexibility beyond that of the stock components provided by Microsoft that work more intuitively than Microsoft tools. Because of the flexibility and intuitiveness of the components, paired with the fact that the components are used consistently across the team it speeds up development time for our ETL solutions. Meta utilizes COZYROC for the following SSIS integration task. Excel Task, Send Mail Task Plus, Excel Destination Plus, Excel Source Plus, Lookup Plus, Dynamics CRM Destination, and Dynamics CRM Source.
Mark LannersMetaBank
The product is user friendly, intuitive, and just works well. Even more importantly, if we have questions of any kind, we always receive a very prompt and knowledgeable response for support. Our company, Altai Systems focuses on non-profit membership organizations. Each project presents extreme data migration and transformation issues. Using a SSIS based tool like COZYROC has make our lives a lot easier.
John KlemetsrudSenior CRM Business Consultant and Guru for Altai Systems, Inc.
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.