Data Migration from Microsoft SQL Database to Dynamics CRM

In this article, I will discuss about migrating data from SQL Database to CRM. This article assumes that you have the following or equivalent:

• Windows Server 2012 R2
• Scribe Insight
• Microsoft SQL Server 2012

Step 1: Creating Source ODBC Connection

1. On Windows Server 2012, go to Control Panel → System and Security → Administrative Tools and select "ODBC Data Sources (32-bit)".
2. Select "System DSN Tab". Click "Add" button.  In "Create New Data Source" window, select "SQL Server" and click the Finish button.
3. Type in a name in "Name" field.
   Type "." (Dot without quotes) in "Server" dropdown.
   Select "Next", then "Next".
   Select the checkbox "Change the default database to" and from the dropdown, select the required database name.
   Select "Next" and select "Finish". Select "Test Data Source" button to verify if the operation is successful. Close the dialog.

Step 2: Creating Source SQL View

Create a view in SQL using the following code:

CREATE VIEW dbo.[ViewName]
AS
SELECT
        [ColumnNames]
        IsProcessed,
        Comments
FROM dbo.[TableName] WHERE IsProcessed = 0

The view ensures that you are only selecting the required columns to migrate. Note the "IsProcessed" and "Comments" columns. You will need to create the "IsProcessed" column in the source table using the following code before creating the view.

ALTER TABLE dbo.[TableName] ADD IsProcessed BIT NOT NULL DEFAULT(0)

The "Is Processed" column will be used to mark the processed records or the records that have been migrated. We will later see in this article how it is useful for scheduling the Scribe package.

The "Comments" column is a Text or Nvarchar field in SQL table. Always put the Nvarchar fields like "description" etc. at the end of the SELECT column list. Otherwise the values will be exported as NULL in the target CRM even if they are present in source. This is a limitation of Scribe and we cannot do much about it.

Step 3: Creating Field in Target CRM Entity

We are assuming that the data will be migrated from Contact table in SQL Database to the Contact entity in CRM. But before we create the Scribe package, we first need to create a Single Line of Text field, length 36, in the Contact entity in CRM. Let’s name it as dataSourceId. Note that the publisher name will be prefixed with this name so the name of the resulting field will be like mypub_dataSourceId. This field is helpful to delete the migrated data later if we face any issue or an error occurs while we execute our Scribe package. It is also helpful later to update the migrated data with the value of any missing field.

We also have the option to migrate the same GUIDs from source SQL table to destination CRM. In that case, we do not need the dataSourceId field to update the data but even then, we will not be able to track if the record has been migrated from SQL or the record has been created in CRM. Therefore, creating this field is always recommended.

Step 4: Creating the Scribe Package for Migration

Click Scribe Workbench icon to open a blank package file.

Figure 1

Now we need to specify the source and target connections for data migration.

Creating Source Connection

Click on Configure Source → Connections → Add as shown in the figure below:

Figure 2

Expand the "ODBC Data Sources" list item and select the name of ODBC Connection that you created in Step 1. Then select the OK button. On the next screen, leave the "Connection Name" as it is or change it if you want. Select OK and then select "Close"

On the next screen as shown below, select the connection name that you just created.

Figure 3


Expand the "Views" list item and select the view you created in Step 2. Select OK. The view columns should now be listed in the Source section of Scribe Package.

Save the package by selecting the Save Icon on the toolbar or using Ctrl+S key.

Creating Destination/Target Connection

Click on Configure Steps → Connections → Add button as shown in the figure below, and select "Microsoft Dynamics CRM" adapter. Then select OK.

Figure 4

On the next screen as shown below, enter the required information.

Figure 5

In the above screenshot:

• The server URL is the base URL of your CRM Website, for e.g. http://www.yourcrm.com/.
• Organization name can be retrieved by going to Settings → Customizations → Developer Resources. Then look for the value under the "Organization Unique Name".
• User and Password values are same as used to log on to the CRM.

Select OK. On the next screen, change the connection name to "DestinationCRM" or whatever you like. Select OK. Then select Close.

On the next screen, select the new CRM connection that you just created. Then select the entity into which you want to migrate the data, in this case it is "contact". Then add the "Update/Insert" step as shown below.

Figure 6

As you can see in the above screenshot, the exclamation mark in the step box shows we need to fix something. For that, after selecting the "Close" button, select the "Lookup Criteria" tab as shown in the figure below. Then select the ContactId from source and dataSourceId from the target and select the "Lookup Link" button.

Figure 7

The Lookup field should now appear under the lookup tab. This tells the scribe to match the contact id in source with dataSourceId in destination. If they match, update the record, otherwise add a new record. It also implies that when we run the package for the first time, all the records will be created. After that if we execute the same package, the records will be updated as the contactId in source will be matched with dataSourceId in destination. If you again select the "Configure Steps" button, you will notice that the exclamation mark has disappeared.

Now we will map few sample fields to migrate the data. First select the "First Name" from source and "First Name" from target. Then select the "Data Link" button. This simply implies that migrate the first name in source to the first name in destination. Repeat the same steps for "Account Id" and "parentcustomerid", as shown in the figure below:

Figure 8

When we double click on the Step Name as highlighted in the figure above (point no. 4), the formula editor will open. Then, execute the steps as mentioned in the figure below:

Figure 9

Pay attention to the highlighted sections. The DBLookup function implies that pick the source value (S11), use the target connection "DestinationCRM", query the account entity and try to match the source value with accountid. If a match is found, return the accountid. If a match is not found, the DBLOOKUP function will return NULL. Select OK to close the window. If we translate the above formula into SQL, we will get the following:

SELECT accounted FROM DestinationCRM.account WHERE accountid = S11

You can map other fields as required and use different Scribe Insight functions. Use the link Scribe Insight Functions for further reference.

Before you run the package, make sure that you execute the steps as shown in the figure below:

Figure 10

This will ensure that if an error occurs while you execute the package, you will get all the information about the failed records and error description in the Scribe table (REJECTED_CONTACTS) that can be viewed through SQL Server Management Studio later on.

Note the "Test" and "Run Job" commands under the Run menu. It is always recommended to use the "Test" command initially to see the actual data that will be inserted after migration. It is also helpful to test see few records and identify any problems in the data and fix them before running the package.

After some testing, you can run the package by using the "Run Job" command. If you want to schedule the package without running it, close the package and see the next section Scheduling the Scribe Package for more information.