Insert, update and delete records in CRM 2013 with SSIS

Case
I have a business application with clients, accounts and other data and I want to make an interface to Microsoft Dynamics CRM 2013. How do you insert, update or delete records in CRM with SSIS?

Solution
Although the CRM data is stored in a regular SQL Server database, you’re not allowed to insert or change the data in those tables (otherwise you will lose Microsoft support on CRM).

There are a couple of third party CRM destination components available like CozyRoc and BlueSSIS. This solution uses a Script Component that inserst/updates/deletes data in CRM via a webservice and a CRM SDK assembly. There are other .Net solutions with for example a custom assembly or a service reference. All roads lead to Rome, but this is a relatively easy script for those with less .Net skills.

1) Guidlines

The first part of this solution will probably be different for everybody, but there are a few guidelines.

1. To make sure you don’t insert records twice, you need a key from your business application in CRM. That could be a hidden field in CRM, but it allows you to check whether the record already exists in CRM.
2. To update or delete records in CRM you need the guid (the unique key or entity id) from the CRM record.

So you want to make a lookup or a join to the CRM view to get the required data.

3. You probably don’t want to update records unnecessarily. This is slow and pollutes the CRM history.

In this solution I selected the exact same columns from CRM and added the GUID. I joined the two sources on the business key with a left outer join. If the business key on the CRM part is null then it’s an insert else it could be an update.

You could compare all column the check whether you need an update or not, but because I have about 20 columns to check this could end up in a huge, unreadable and unmaintainable expression. I used a checksum transformation to calculate a hash from all columns and then I only have to compare those two hashes. You could also do this in the source query with some TSQL code.

example package

Below I will describe those Script Components.

2a) Download CRM SDK
For this example I used CRM 2013 and I downloaded the free Microsoft Dynamics CRM 2013 Software Development Kit (SDK). Execute the downloaded file to extract all the files. We only need Microsoft.Xrm.Sdk.dll assembly which can be found in the SDK\Bin folder.

SDK download

2b) Windows Identity Foundation
The CRM SDK also requires an installation of Windows Identity Foundation. I used Windows6.1-KB974405-x64 for this example.

3) DLL to SSIS machine
To use the assembly (DLL) from step 2a in SSIS, you need to add the DLL to the Global Assembly Cache (GAC) on your SSIS machine. Here is an example for adding to the gac on Win Server 2008 R2. You also need to copy it to the Binn folder of SSIS: D:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\

4) Parameters
To avoid hardcoded usernames, domainnames, passwords and webservices, I created four project parameters (SSIS 2012). These parameters will be used in the Script Components.

4 parameters, password is marked sensitive.



5) Script Component - Parameters
Add a Script Component (type destination) for Insert, Update or Delete. When you edit the Script Component make sure to add the four parameters from the previous step as read only variables.

Add parameters as read only variables

6) Script Component - Input Columns
Add the columns that you need as Input Columns. For the Insert you only need the columns from your business application. For the update you also need the entity ID from CRM. This is the Technical Id (a guid) from the CRM entity that you want to update. For a delete you only need that entity ID.

Input Columns for insert

7) The Script - Add assembly
Hit the Edit Script button to start the VSTA editor. In the solution explorer you need to add three references:

• microsoft.xrm.sdk.dll (from the SSIS bin folder mentioned in step 3, use browse)
• System.Runtime.Serialization.dll (from .Net tab)
• System.ServiceModel.dll (from .Net tab)

Right click references and choose add reference

Now very important: press the Save All button to save the entire internal vsta project (including references)

Save All

8a) The Script - Insert
Here is an C# example (for VB.Net use this translator) for inserting CRM records with SSIS 2012.

// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.Xrm.Sdk;                // Added
using Microsoft.Xrm.Sdk.Client;         // Added
using Microsoft.Xrm.Sdk.Query;          // Added
using System.ServiceModel.Description;  // Added

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

    // Variables for the CRM webservice credentials
    // You could also declare them in the PreExecute
    // if you don't use it anywhere else
    string CrmUrl = "";
    string CrmDomainName = "";
    string CrmUserName = "";
    string CrmPassWord = "";

    // This method is called once, before rows begin to be processed in the data flow.
    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;
        organizationservice = new OrganizationServiceProxy(new Uri(CrmUrl), null, credentials, null);
    }
  
    // This method is called once for every row that passes through the component from Input0.
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Create a Entity object of type 'account'
        Entity newAccount = new Entity("account");

        // Store the business key of the source in CRM
        // This makes it easier to compare and filter records for update
        newAccount["cst_caressid"] = Row.CaressId;
        
        // fill crm fields. Note fieldnames are case sensitive!
        newAccount["name"] = Row.AccountName;
        newAccount["emailaddress1"] = Row.Email;
        newAccount["telephone1"] = Row.Phone;

        // Address, but check if the columns are filled
        if (!Row.Street_IsNull)
        {
            newAccount["address1_line1"] = Row.Street;
        }

        if (!Row.Housenumber_IsNull)
        {
            newAccount["address1_line2"] = Row.Housenumber;
        }

        if (!Row.Zipcode_IsNull)
        {
            newAccount["address1_postalcode"] = Row.Zipcode;
        }

        if (!Row.Residence_IsNull)
        {
            newAccount["address1_city"] = Row.Residence;
        }
       
        if (!Row.Country_IsNull)
        {
            newAccount["address1_country"] = Row.Country;
        }

        // Filling a OptionSet (dropdownbox) is a little different
        // You need to know the codes defined in CRM. You need
        // CRM knowledge to find those so ask the CRM consultant.
        OptionSetValue accountType = new OptionSetValue();
        if (!Row.AccountType_IsNull)
        {
            switch (Row.AccountType)
            {
                case "Large":
                    accountType.Value = 1;
                    break;
                case "Medium":
                    accountType.Value = 2;
                    break;
                case "Small":
                    accountType.Value = 3;
                    break;
                default:
                    accountType.Value = 2;
                    break;
            }
            newAccount.Attributes.Add("accounttype", (OptionSetValue)accountType);
        }

        // Reference to an other entity (lookup)
        EntityReference Contact = new EntityReference("contact", Row.ClientGuid);
        newAccount["contactid"] = Contact;


        // Create account
        organizationservice.Create(newAccount);
    }
}

8b) The Script - Update
Here is an C# example (for VB.Net use this translator) for updating CRM records with SSIS 2012.

// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.Xrm.Sdk;                // Added
using Microsoft.Xrm.Sdk.Client;         // Added
using Microsoft.Xrm.Sdk.Query;          // Added
using System.ServiceModel.Description;  // Added

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

    // Variables for the CRM webservice credentials
    // You could also declare them in the PreExecute
    // if you don't use it anywhere else
    string CrmUrl = "";
    string CrmDomainName = "";
    string CrmUserName = "";
    string CrmPassWord = "";

    // This method is called once, before rows begin to be processed in the data flow.
    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;
        organizationservice = new OrganizationServiceProxy(new Uri(CrmUrl), null, credentials, null);
    }
  
    // This method is called once for every row that passes through the component from Input0.
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Create a Entity object of type 'account'
        Entity existingAccount = new Entity("account");

        // Most important attribute to fill is the entity id
        // This is a GUID column from CRM. Without this
        // column you can't update records in CRM.
        existingAccount["accountid"] = Row.AccountId;
  
        // Since we joined on the business key, it shouldn't
        // be updated. That why this line is a comment.
        // existingAccount["cst_caressid"] = Row.CaressId;
        
        // fill crm fields. Note fieldnames are case sensitive!
        existingAccount["name"] = Row.AccountName;
        existingAccount["emailaddress1"] = Row.Email;
        existingAccount["telephone1"] = Row.Phone;

        // Address, but check if the columns are filled
        if (!Row.Street_IsNull)
        {
            existingAccount["address1_line1"] = Row.Street;
        }

        if (!Row.Housenumber_IsNull)
        {
            existingAccount["address1_line2"] = Row.Housenumber;
        }

        if (!Row.Zipcode_IsNull)
        {
            existingAccount["address1_postalcode"] = Row.Zipcode;
        }

        if (!Row.Residence_IsNull)
        {
            existingAccount["address1_city"] = Row.Residence;
        }
       
        if (!Row.Country_IsNull)
        {
            existingAccount["address1_country"] = Row.Country;
        }

        // Filling a OptionSet (dropdownbox) is a little different
        // You need to know the codes defined in CRM. You need
        // CRM knowledge to find those so ask the CRM consultant.
        OptionSetValue accountType = new OptionSetValue();
        if (!Row.AccountType_IsNull)
        {
            switch (Row.AccountType)
            {
                case "Large":
                    accountType.Value = 1;
                    break;
                case "Medium":
                    accountType.Value = 2;
                    break;
                case "Small":
                    accountType.Value = 3;
                    break;
                default:
                    accountType.Value = 2;
                    break;
            }
            existingAccount.Attributes.Add("accounttype", (OptionSetValue)accountType);
        }

        // Reference to an other entity (lookup)
        EntityReference Contact = new EntityReference("contact", Row.ClientGuid);
        existingAccount["contactid"] = Contact;
  
        // Update account
        organizationservice.Update(existingAccount);
    }
}

8c) The Script - Delete
Here is an C# example (for VB.Net use this translator) for deleting CRM records with SSIS 2012. It wasn't mentioned in the solution example. You need a full outer join for it. Warning: this is a physical delete which can't be undone. In an other post I will publish an inactivate script example, but it uses other assemblies.

// C# Code
using System;
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.Xrm.Sdk;                // Added
using Microsoft.Xrm.Sdk.Client;         // Added
using Microsoft.Xrm.Sdk.Query;          // Added
using System.ServiceModel.Description;  // Added

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

    // Variables for the CRM webservice credentials
    // You could also declare them in the PreExecute
    // if you don't use it anywhere else
    string CrmUrl = "";
    string CrmDomainName = "";
    string CrmUserName = "";
    string CrmPassWord = "";

    // This method is called once, before rows begin to be processed in the data flow.
    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;
        organizationservice = new OrganizationServiceProxy(new Uri(CrmUrl), null, credentials, null);
    }
  
    // This method is called once for every row that passes through the component from Input0.
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Warning: this is a physical delete from CRM which can't be undone
  
        // Delete account. First part in the entityname, second
        // is the entity id from the CRM source.
        organizationservice.Delete("account", Row.AccountId);
    }
}

Note: The method above is called late binding. You can also use early binding, but late binding is apparently faster.

// C# Code
// Early binding
Account newAccount = new Account();
newAccount.Name = "SSISJoost";
organizationservice.Create(newAccount);

// Late binding
Entity newAccount = new Entity("account");
newAccount["name"] = "SSISJoost";
organizationservice.Create(newAccount);