Data Migration Strategy

With these steps you can successfully develop a data migration strategy, but if you are migrating from a Dynamics CRM on-premise instance, you need to follow a different path. Based on the system that you are on, you need to move to Dynamics 365 on-premise by applying the relevant upgrades. Only then can you move the on-premise instance to the online version. This is known as Microsoft FastTrack for Dynamics 365. In the next section, I will give a brief introduction to this process.

Microsoft FastTrack for Dynamics 365

You must go through a set of steps like these based on the on-premise version that you are in. The FastTrack approach is guided and managed through FastTrack or solution architecture CXP teams. Nominations for the FastTrack program are accepted through the FastTrack portal: https://www.microsoft.com/microsoft-365/partners/fasttrack . Once the nomination is approved, a FastTrack engineer will be assigned to you, and they will work with you through the end of the migration process. You can find more about the program at https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/admins-customizers-dynamics-365/mt812191 (v=crm.8).

In the sections to follow, you will look at some of the other options available to migrate data to Dynamics 365.

Import Wizard

Dynamics 365 is equipped with a Data Import Wizard that can be used to import data into your Dynamics 365 instance. It accommodates simple data imports and supports the .csv, .xml, .txt, and .xlsx file types. It also allows zipped files, which include the previous file types. Let’s look at a simple example where you can use the Data Import Wizard. Figure 9-1 shows a CSV file consisting of a list of members. Note that the Membership Type field is a lookup field, and the Payment Method field is an option set in your destination system. Also on the first line of the data dump are the field names.

Now navigate to the Settings and in the Data Management section select Import Data. See Figure 9-2.

On the Upload Data File screen, browse and select the CSV file to upload the data and click Next. You can also drag and drop the files from the source to the window. See Figure 9-3.

On the next screen, you can review the file upload summary that displays the number of files, the file size, and the delimiter settings. As you can see, we have selected the “First row contains column headings” check box. See Figure 9-4.

On the next screen, you must select the data map before the import. If you have created a data map and imported it into the Dynamics 365, then you can select it from here. For this example, we will select the default option and will map the source and destination field names manually. See Figure 9-5.

Next, select the record type; in this example, it is the member record. See Figure 9-6.

As the final step, you will map the fields. As mentioned earlier, select the “Look up” field and the option set value. Map them as shown in Figure 9-7.

Click Next, and on the last screen click the Submit button to submit the data for import. Once you click Submit, you will see the progress when you refresh the list. When the import is successfully completed, you will see the number of records successfully imported and the failures. See Figure 9-8.

When import completes, you can navigate to the Members list and see the actual data that has been imported, as illustrated in Figure 9-9.

For more complex data imports, you should consider using SQL Server Integration Services. In the next section, you will take a quick look at the options available.

Data Importing with SSIS

Using SQL Server Integration Services is a great way to migrate data from different sources to Dynamics 365. The KingswaySoft ( https://www.kingswaysoft.com/products ) and CozyRoc ( https://www.cozyroc.com/products ) SSIS toolkits enable you to create effective and efficient scripts that move data from the source to the destination. These tools are third-party components, so you must buy a license from the vendor. But, you can easily download the developer version of both these software without any cost and try them. For instance, when you install the developer version of the KingswaySoft SSIS package, you can run the SSIS packages within your development tools such as Visual Studio. The tool is fully functional under the developer license, and the only difference between the licensed version is that you cannot operate it outside the developer tools, for instance scheduled execution. But there is an exception that is worth mentioning. If you want to test scheduled packages, you can acquire a 14-day trail license that will revert to a developer license after 14 days.

In this section, you will look at inserting a list of members and primary contacts into Dynamics 365. First, you must install SQL Server Data Tools for Visual Studio 2017. Visit this link to find out more details: https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017 . Next, download and install the SSIS components from the Kingsway Soft product page. Then, create the project to include the data migration scripts. Under New Project, select Business Intelligence and select the Integration Service Project template. Give the project a meaningful name and save it.

When the project is loaded, the SSIS Toolbox will also be loaded, and you can see the components. As the first step of creating the script, you must insert a component such as the Data Flow task on the Control Flow tab. The Data Flow task defines the data from a source to a destination and defines the transformation while moving data. Within the Control Flow tab of the package, you can have multiple data flow tasks and manipulate them using the containers such as the For Loop, Foreach, and Sequence containers. When you double-click or click the Data Flow tab, you can define the actions to perform for that particular data flow task.

Creating the Connection Manager

For this example, you will use four data flow tasks. Add the data flow task and connect them with the green arrow. Select the first task and open the Data Flow tab. Since you are going to read the data from the CSV file, you must create a connection to the file. At the bottom of the screen, you can see the Connection Managers tab. Right-click the view and select New Connection, and from the list of connections, select Flat File Connection and click Add. This will open the configuration window. See Figure 9-10.

In this window, you will be selecting the location of the file and leaving the other settings as they are. One important thing to mention here is to check the “Column names in the first data row” option if the first row contains column names. Click OK, and the new connection will appear on the Connection Managers tab.

Configuring the Source

Now, select Flat File Source in the toolbar. See Figure 9-11.

In the Flat File Source editor, you can select the new connection from the drop-down. When you click the Preview button, you can see a preview of the data that is to be loaded. See Figure 9-12.

In this window, you can see that all the columns were loaded. For the first step, you are planning to load the contacts only. Therefore, from the Columns tab, select the columns you want. See Figure 9-13.

You’ve established the source connection, and the configuration has been completed. Since there is no transformation is required, you will simply use the Dynamics CRM destination. Before that, you are going to set up the connection manager for the Dynamics CRM destination. As previously explained, select the Dynamics CRM connection manager from the SSIS Connection Manager. When you provide the details of your Dynamics 365 instance, it should look something like Figure 9-14.

Configuring the Destination

Now, from the Toolbox add the Dynamics CRM Destination component and configure. See Figure 9-15.

As shown in Figure 9-16, set the destination settings. As you can see, we have selected the connection manager that we created. The action is set as Upsert, which is a great way of performing an insert and an update at the same time. What does this mean? As per this example, you are importing the contacts, and if a contact exists, the destination component will go and update existing contact. If the contact does not exist, it will create a new one. This action is determined by the upsert/update settings you specify. There are few options available here. For the purposes of this example, you will be manually specifying the matching criteria, and if there are any multiple matches, you will raise an error. See Figure 9-16.

On the Columns tab of this window, we have selected the required columns to be updated for the contact record at the destination instance. As you can see in Figure 9-17, three columns have been selected as key columns. This where the Upsert/Update Matching Criteria field is configured, which means based these fields, the package will look for any matching records.

Configuring Error Handling

Now it’s time to configure error handling.

On the Error Handling page, choose the “Redirect rows to error output” option in the “Choose error-handling mechanism” section to direct any duplicate rows to be written to a flat file so that you can examine them, correct the errors, and re-upload them.

Sometimes if you did not give much consideration to the data types and the size of the fields, you will end up in failures during the migration, which makes it difficult to troubleshoot. To make the matters worse, you might end up rolling back data, which will result in incomplete records or data inconsistencies. This is important at the time of transformation. That is, you must take all the measures you can to make the data types and field size match the target system’s field data types and sizes. Ideally, the output fields from the transformation should match the target system’s fields exactly. This can be detected with error handling, but you should try to avoid this type of issue in the first place. See Figure 9-18.

Add another flat-file destination with a new flat-file connection manager and map the error fields as illustrated in Figure 9-19 to ensure the errors are written to a new file.

Once everything is set up, your data flow task should look something like Figure 9-20. When connecting the Dynmics CRM destination to the flat-file destination for error logging, make sure you select the red line, which defines the error pipeline.

Data Transformation

Now that you have created the first data flow to load the contacts, let’s create the next data flow to insert the member records. Again, you will read the same CSV file to retrieve the member details, and therefore you will add the flat-file source and set the connection like you did in the previous step. As shown in Figure 9-21, there is a new component added to the flow. It is a script component that you can code with C#. Within this component, you have transferred the renewal date to a date-time value.

When you double-click the script component named Data Transformation, a settings window will appear. In this window, on the Input Columns tab, you can define which columns should be pushed down the pipeline. See Figure 9-22.

At the bottom of the window, click the Edit Script button to load the C# editor. For this example, you have simply converted the renewal date in string format to a date-time format. During a transformation there are three events: Pre Execute, Post Execute, and Transformation. As per the comments in the code, the Pre Execute event is triggered once before processing the rows, and Post Execute will be triggered once after processing all the rows. All the transformation is carried out within the Input0_ProcessInputRow method. The parameter Input0Buffer contains all the rows that you are passing to the script component. The nice thing about this method is that all the records in the input buffer will be iterated and processed. See Figure 9-23.

Configuring the Dynamics CRM destination is the same as discussed earlier. But for the demonstration purposes, there are slight differences in the settings. After completing the membership update, the next configuration is to update the contact record with the company details or the member company. This one is a bit more complex than what you did in the previous two tasks. See Figure 9-24.

As per the requirements, you have selected both accounts and contacts using two separate Dynamics CRM data sources. Your plan is to merge the outputs together. To do this, you have to sort the data sets separately, and you can use the Sort component from the toolbox. Sorting is a must for merging records. In the Sort Transformation Editor window, you specify the name (member name) as the sorting field. The same setting was applied to the contact list retrieved as well.

Note

This merge and join scenario might not work for extremely larger data import scenarios. This is primarily because SSIS will create a temporary data store to perform the merge and join. It would be a better option to create a Fetch XML query with the join and then filter the records. In the previous example, the Dynamics CRM source component is configured to directly query the entity. As shown in Figure 9-25, when you change Source Type to FetchXML, you can enter the Fetch XML query. You can use the Fetch XM Builder tool in XrmToolBox to build your Fetch XML query. See Figure 9-26.

Configuring the transformation and the destination is the same process as you did earlier. For the final step, the membership record must be updated with the primary contact; we have used the contact record that was created in the first step. See Figure 9-27.

For this task, you retrieve the contacts and select the company name and the contact ID. The company name is used to match the account. Again, the configuration and transformation are the same as earlier. The final outcome of all the data flow tasks will be something similar to the one illustrated in Figure 9-28. The check mark indicates that each step has executed successfully. If there are any errors, you will see a red X on the component that failed.

When you run a package from Visual Studio, you can see the progress, and if any step fails, the execution stops at that point. In such scenarios, you can open the Progress tab of the package and troubleshoot the issue. As a best practice, it is always good to try the package against a test instance before doing the actual migration. As you can see in Figure 9-29 and Figure 9-30, the contact and the membership record have been successfully updated with the contact details.

Not only for data migration tasks, these SSIS packages are ideal for bulk data updates and scheduled processes. Since the updates are done using the SDK through the destination components, you will have full traceability as well. CozyRoc is similar to KingswaySoft components with minor differences and with pricing differences as well. You have the option to select the tool that best fits your client’s specific scenario.

Summary

In this chapter, you learned about moving data from your legacy system to Dynamics 365. At the beginning of the chapter, a good explanation was given to highlight the importance of setting up a strategy. Next you took a quick peek into the Microsoft FastTrack program, and finally you looked at the out-of-the-box tools and the custom tools that can be used to facilitate the tedious task of data migration.