Integrating Text File Records in Dynamics CRM with SSIS Integration Cycle Process

Dynamics CRM has a complete import module that could resolve simple and complex data imports from XML or CSV file types. It is very powerful when you need to create or update records for entities or, maybe if you are using an Office 365 subscription, you will be able to modify data directly from Excel online. I'm sure you already know these features.

Today we're going to talk about an external process to upload files into Dynamics CRM. In some scenarios, you will need to move data from one system to another, as CRM and this source system could only export their own data to a text file.

If the import will need to run each day at a specific time and you need to move this file to another directory when it's done, the best approach is to use a SQL Server Integration Services process and Dynamics CRM SDK.

In order to understand the process, I will:

• Define a text file format for the source.
• Read it from SSIS (read all files that could have a file system path).
• Map and transform data types from source.
• Invoke a SSIS Script task to talk with Dynamics CRM SDK (organization Service).
• Move processed files to another folder.

Define a Text File Format:

For the development environment, you could set the "File Path Directory" for source use as "C:\MyImportProcess\"

This file will have rows and each row represent a record to insert/update in Dynamics CRM.

Suppose we use the contact entity:

Each position in the text file represents a field to be mapped to a contact entity. For this example, the first position is the "contact number," the second is the "firstname," "lastname," "date of birth," "document type," and "document number."

Read it from a SSIS Process

OK, we have a text file definition and the contact entity personalized to support this data. What's next?

We need to use the Visual Studio and SQL Server Data tools to create a SSIS Project.

Our process will look like this:

• Create a staging table to move records from text file to a SQL Table (Execute SQL Task – SSIS Tool)
  • This is for open a connection to a file – move to a table and close the connection. The performance will improve with this action.
  • For our example, we should create a "contact staging" table with the same columns as the text file
  • It is a good practice to clear the staging table before starting to use it.
• I will use the Query Path task (Execute SQL Task – SSIS Tool) to select the file name. We need it later to move the file from one path to another.
  • We could use a syntax like: “select C:\MyImportProcess\as path_file" when "path_file" is a local variable.
• The Loop File process (For each loop container – SSIS Tool) will cycle into all the files that found in the source path.
  • For each file we need to:
    • Read columns from the CSV file.
    • Add a variable column with the source path file name.
    • Insert rows in the staging table.

• Create Records in CRM

  • In order to create records in CRM we will use a "DataFlow Task."

• Read from staging will select the rows inserted in staging and map out to use in the "Script component" – Create Record

• Create Record: This is a SSIS script component that allows us to handle C# CODE!

  • When you add a script component, you will be able to write code that interact with the Dynamics CRM SDK as follows:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using System.ServiceModel.Description;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    IOrganizationService orgService;

    string CrmUrl = "";
    string CrmDomainName = "";
    string CrmUserName = "";
    string CrmPassWord = "";

    public override void PreExecute()
    {
        base.PreExecute();

        // Fill variables with values from project parameters
        CrmUrl = this.Variables.crmwebservice.ToString();
        CrmDomainName = this.Variables.crmdomain.ToString();
        CrmUserName = this.Variables.crmuser.ToString();
        CrmPassWord = this.Variables.crmpassword.ToString();

        // Connect to webservice with credentials
        ClientCredentials credentials = new ClientCredentials();
        credentials.UserName.UserName = string.Format("{0}\\{1}", CrmDomainName, CrmUserName);
        credentials.UserName.Password = CrmPassWord;
        orgService = new OrganizationServiceProxy(new Uri(CrmUrl), null, credentials, null);

    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Entity contact = new Entity("contact");

        if (!Row.firstname_IsNull)
contact["firstname"] = Row.firstname;
if (!Row.lastname_IsNull)
contact["lastname"] = Row.lastname;
if (!Row.documentnumber_IsNull)
contact["new_documentnumber"] = Row.new_documentnumber;

try
{
orgService.Create(contact);
}
catch(Exception e){

//Catch exception
}

    }
}

Look at the "Input0_ProcessInputRow" method. When you create a "SSIS Script component," the Visual Studio automatically generates this method. SQL Server Integration services pass one record each time to this function, and it allows you to process with de CRM SDK for each record all the validations and queries that you will need before insert to CRM.

The "Row" variable has all the fields that you mapped from SQL staging table to the script task input parameters.

Conclusions

As you could see, with a few steps, you can create a Microsoft SQL Server Integration Service that discovers all the files in a directory, maps to a temporary table in SQL, and processes each record with the Dynamics CRM SDK in order to insert in the database.

This is a supported method because you always interact with the Organization Service from CRM and you are not talking directly to the tables or stored procedures of Microsoft Dynamics, which is not supported.

The performance result that you achieve with this approach is very good and better that other solutions like a C# console application or something similar.

Using this tool, you can think on complex models as you want to integrate. For example, a record type that could have a header and detail structure or multiple relationships.

I hope Microsoft CRM solutions have helped you understand the process of uploading files in CRM with SSIS. Still, if you feel like asking any question, or making any comment, go ahead. Write your reviews in below comment section.