I am constantly being asked to import data into Dynamics CRM from various sources, and over time I have discovered that, even though the sources of data that I am importing vary, the process of performing the import is similar. Which tool I choose to use depends heavily on the source of the data, along with the format it comes in, and whether or not any manipulation needs to be performed prior to the actual import.

Even though 3^rd party tools like Scribe are great for doing these data imports, sometimes it isn’t the best fit for a client. There are times when SQL Server Integration Services (SSIS) are a better choice, and I will describe the steps to create an SSIS package to import data into Dynamics CRM. Using the out-of-the-box data import feature in Dynamics CRM is another option, but I would like to focus on the SSIS package in this article.

Data Source Definition

Before we can create an SSIS package to perform the import, we need to define a data source. This may already be defined for your scenario, but for this article I created a SQL database that represents account information.

Figure 1: Data Source for Accounts

Creating the SSIS Package

Once the data source has been defined, we can now use SQL Server Data Tools (SSDT) to create an Integration Services project. This project will define our SSIS package, which has the steps we need to perform to do the data import.

Figure 2: Integration Services Project

When a package is created in the project, it starts you out on the Control Flow tab (Figure 2). I can add either a Data Flow Task or an Execute SQL Task. I’m using a Data Flow Task in the package, as this will allow me to define the data source connection, the destination to import the data to, as well as any steps I want to perform.

The Data Flow tab displays a designer for the Data Flow Task(s) you define in the package (you can have more than one). This designer surface allows you to build out your source and destination connections, along with any manipulation steps you need to perform before the data is imported.

Figure 3: Data Flow Task in SSIS Project

For my package, I don’t need to manipulate the data before I import it into Dynamics CRM, so the only items I need to define in the Data Flow task are the source and destination. For the source, I’m using an OLE DB Source object, and this has been configured to go directly against the Accounts table in the database (Figure 4). I also mapped all the columns, as I want to import all this information, as seen in Figure 5.

Figure 4: Source Connection Definition

Adding the Script Component

For the destination, we need to setup a Script Component as we will be using c# code (using the Dynamics CRM SDK assemblies) to import the data into our Dynamics CRM organization, as shown in Figure 6.

Figure 6: Script Component in SSIS

I then mapped the columns coming from the source data, so that my code can access this to perform the import. Figure 7 shows how I mapped the columns – you can select only the data columns you want to import, and also have the ability to rename the column names (if you want friendly names, for example).

Figure 7: Script Component Column Mapping

Adding Code to Perform the Data Import

Now we are ready to add the code that does the actual import of data into Dynamics CRM. Clicking on the Edit Script… button (Figure 6) will open Visual Studio with a special project type (VstaProjects) that contains a “shell” project which you can add code to.

There are 2 methods that we need to add code to, in order to connect to a Dynamics CRM instance and perform the data import. The first method is PreExecute() and we override this method so that we can first connect to a Dynamics CRM instance using our credentials. Figure 8 shows the format for connecting to a Dynamics CRM Online organization, along with the code (commented out) on how to connect to an on-premises Dynamics CRM organization. Please note that the UserName you use when connecting to CRM Online must be a valid CRM user in the organization you are connecting to.

Figure 8: Visual Studio Project from Script Component

The other method we need to add code to is Input0_ProcessInputRow(), and this method is executed for each row of data coming from the source. We want to create a new Account record in Dynamics CRM for each row, so Figure 9 shows the code that does this. Note that this code can get a lot more sophisticated, based on what you need to do during the import.

igure 9: Code to Import Data

Final Thoughts

I came across a couple of issues when developing the Script Component for my SSIS package and would like to mention them here.

• In order to use the Dynamics CRM SDK, I needed to add references to the Microsoft.xrm.sdk.dll and Microsoft.crm.sdk.proxy.dll files. I found that I received an “Assembly not found” error when running the SSIS package, and this was due to the fact that I needed to copy these DLL files to a specific directory under the local SQL Server installation directory location. Here is a link that describes the problem.
• I also ran across problems when attempting to use early binding in the Script Component Visual Studio project. When I added the cs file generated from the CrmSvcUtil.exe program to the project, I would get the “Assembly not loaded” error when running the project. Adding the DLL files (as described above) didn’t resolve the problem, so I resorted to use late binding.

So, if you are familiar with SSIS and need greater control over your data import process, the process explained here may be an option for you. I hope this article is helpful in providing another option for importing data into Dynamics CRM.