Dynamics 365 Data Migration Using SQL Server SSIS

Prerequisite

  • Install Microsoft Visual Studio (using 2015 in this case).
  • Download and install SSIS Integration Toolkit from Kingsway Soft.

Process

Prepare you source data / structure

Let say we want to export contacts from a SQL database and import that into Dynamics 365. Prepare your source data first. In the following example, we have written a query to export Top 10 Contacts (first name, surname, e-mail, etc.) where the Contact is of Type Person. You can of course, prepare your source data as required.

So, our data is prepared that we would like to fetch from SQL database.

NOTE: In real terms you would be comparing your source data with your destination and creating the query accordingly.

Establish Connectivity

Open up Visual studio, which is the host for SSIS tools area. You don’t necessarily have to have a copy of Visual Studio, you can very well install the SQL Server Data Tools (SSDT) and use that instead.

Create a new Integration Services Project and give it a Name.

One you create the package, it will give all the tools.

Now, we need to establish a connection between the two systems that we are working with (SQL source and dynamics 365 destination). For this we need to create Connection Managers.

Right-click on the Connections Manager area and select New OLE DB Connection.

Click New.

Specify Server Name and Select or Enter a Database Name and click Test Connection.

If connection is successful, click Ok.

So now, we have a source connection and we want to have a target connection. Again right-click on the Connection Managers area and click New Connection.

Select Dynamics CRM (from Kingswaysoft) and click Add.

Configure the CRM Connection Manager by specifying:

  1. Authentication Type – Select “Online Federation (CRM Online – Offfice 365)
  2. CM Discovery Server – Select the option based on your location, for example cm6 is for Australia (Oceania region).
  3. User Name / Password – Provide your Office 365 credentials.
  4. Organization – Select you organization you are subscribed to.

Test Connection and click Ok.

So now we have connections to the SQL database and Dynamics 365.

Map Source to Destination

Insert a Data Flow Task from the SSIS tool box to the Control Flow tab. This means that inside this task we are going to move data from point A to point B.

Now, click Data Flow tab and insert OLE DB Source. You can edit the name of the OLE DB Source and also Edit to configure the settings. For example, in this case we have specified the SQL command which we prepared earlier.

You can also Preview the result of the query. If everything is alright, click Ok.

So, we have a source. Now, we would like to choose a Dynamics CRM Destination object to the Data Flow tab.

Also, drag the blue line from the source and link it to the destination. This means that we are connection the success output from source to the input of the destination.

Now, Edit the Dynamics CRM Destination and configure the CRM Destination Component Editor by specifying:

  1. CRM Connection Manager (select from drop down).
  2. Action (for example, here we want to create new contacts).
  3. Destination Entry (select the CRM table).

To map the fields, click Columns and map the fields from source to destination and click Ok.

Execute

On the Data Flow tab, right-click and click Execute Task.

This will execute the connection and, show you the progress. Once done, the system will also display the number of rows processed.

You can now check the result in Dynamics 365

Data Migration Using Assisted Setup

Click here to view all NAV related blog posts.

With Microsoft Dynamics NAV 2017 you can use assisted set up to perform certain set up tasks based on the wizard. The data migration is one of these tasks. This is what we call the data migration using assisted set up. This is based on a wizard.

You can import from Excel, but you can also import from other finance solutions, for example QuickBooks. This is using the extension management in Microsoft dynamics NAV, which means that in real life you can use these extensions and also build extensions to use other finance software applications.

Click Departments/Administration/Application Setup/General/Assisted Setup.

Select Migrate Business Data and Start Setup. Follow the instructions:

The import process is the same as using Rapid Start. The only difference is that here we are working with a wizard.