CRM 2011 Data Import/ Export using Cozy Roc SSIS library

A CRM system is only as good as the data in it. Along with entering data using the UI, there are multiple needs around a CRM supported data import application. For MS Dynamics Crm2011, there are a quite a few options available, like the native CRM Import wizard, Scribe or SSIS applications like Cozy Roc.

In this post I am going to walk through a very basic crm import/ export using the CozyRoc SSIS+ library. CozyRoc SSIS+ library includes Dynamics CRM adapters with support for all three deployment types: Premise, Hosted and Live. A SQL Server license is required to use the library.

If there are complex data transformations required, SSIS is one of the best options available. The whole ETL process with Dynamics CRM can be completed in SSIS using CozyRoc library without any programming skills or knowledge of using the CRM webservice.

You can use the library for migrations without paying for a license, if you don't do automation under SQL Job Agent. Please contact CozyRoc for the latest license details.

Importing Data into Dynamics CRM 

1. Download and install the latest CozyRoc SSIS+ library from http://www.cozyroc.com/

2. Open BIDS. Create a new project called CRM_Account_Import.

3. Open your Toolbox, right-click and select the Choose Items... menu. Click on SSIS Data Flow Items tab in the dialog and check Dynamics CRM Destination and Dynamics CRM Source. Click Ok.

4. Add a Data Flow task in the Control Flow tab. Click on the Data Flow tab. Add a OLE DB Source (I am importing from a db table in this example) and a Dynamics CRM Destination Control Flow Item. Connect the two.

5. Add a OLE DB connection. For CRM, right click on the Connection Managers Section and select New Connection. In Add SSIS Connection Manager Dialog, pick Dynamics-CRM and click Add.

6. Enter the details to authenticate the web service call. Remember that the user should have the requisite permissions to load/ update/ delete data in CRM. 

7. Double click on the OLE DB Source and set the values for the connection manager and source table.

8. Double click on the Dynamics CRM Destination item and set the values for the connection manager in the first tab.

9. In the Component Properties tab, under section Custom Properties, set the following values:

Action: Create

DestinationEntity: Account

DetectDuplicate: False

IgnoreMissingIdentifier: False

Here are more details on the cozy roc website: http://www.cozyroc.com/ssis/dynamics-crm-destination.

10. In the Column Mappings tab, map the values from your source to the account entity. In this example, I am mapping only the name attribute in the account. 

Remember that even though the account name is a required field in the UI, when creating the account using the crm webservice, an account can be created with no name at all, though it does not make a lot of sense to do something like that! So pay extra attention when mapping values to make sure the basic UI required fields are filled in to avoid missteps due to UI validation.

11. Click Ok to close the Dynamics CRM Advanced Editor. Save the package. Hit F5 on your keyboard or the Start debugging button to run the dtsx file. After the process is complete, test the results by going to the Active Accounts view.

Exporting from Dynamics CRM.

When exporting data from CRM, in the Data Flow tab, add a Dynamics CRM Source Control Flow Item and a OLE DB Destination(I am exporting to a db table in this example). 

Set the OLE DB destination settings. Double click on the Dynamics CRM Source item and set the values for the connection manager in the first tab.

In the Component Properties tab, under section Custom Properties, choose the Entity name. The Input Type can either be all the records in the entity, or you can use a FetchXML from an advanced find. The max value for BatchSize is 500. More details can be had here : http://www.cozyroc.com/ssis/dynamics-crm-source

Once the columns are mapped, test the package and verify that the date was exported correctly.

A couple of disclaimers before i bid aideu:

1. Please confirm the latest licensing requirements with Cozy Roc before using the product.

2. The example that I had is very vanilla. I have not used the Cozy Roc library in production, or for a complex multi step ETL process.