Saturday 25 January 2014

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.
  1. 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);

24 comments:

  1. Hello - First off great post, it has helped me out a ton! Also the other posts in this set. One thing I am trying to do that I cannot figure out is I need to insert data into LookUp field types in CRM. Have you had any experience with this, or resources for this?

    ReplyDelete
    Replies
    1. Yes, search the page for EntityReference (8B).

      Delete
  2. Awesome, not sure how i missed that! Thank you for pointing it out. Now if I can just figure out how to get the GUIDs needed. :)

    ReplyDelete
    Replies
    1. If you're using SSIS then you can use a lookup transformation on the CRM views. If not then you could query the same view in C# with LINQ or via an OleDbCommand

      Or check out the CRM forum on MSDN

      Delete
  3. Hey Joost. I've used your solution to create an interface between 2 systems, inserting and updating records. It worked fine in CRM 2011, we have migrated to CRM 2013 and the updates now don't work. The script error says the GUID doesn't exist but it's definitely there. Any ideas? Thanks Alan

    ReplyDelete
    Replies
    1. Did you use the 2013 SDK? And is there only one SDK version installed in the GAC?

      Does the error occur on runtime? Is it an error from the webservice?

      Delete
    2. Yes, I downloaded the 2013 sdk v6.0.4, there is only one xrm.sdk.dll in the GAC, it is the correct version. Running the SSIS package in debug mode, it fails on the dataflow update script step, the Script Component Runtime Error : xcor_incidentvehicle With Id = 69a2e220-14cb-e311-93f6-02bfac10b590 Does Not Exist. If I change the config to look at a CRM 2011 endpoint and database view, it runs with no errors. Ta

      Delete
    3. are you sure you used the right CRM db as source? Is that DB pointing to the same CRM environment as your webservice?

      Delete
    4. Yes, as I can see the records that have been inserted which are using the same parameters as updates, its just updates that fail.

      Delete
    5. Ok and you're sure that the guid that failed is available in the same table as where your records were inserted. The only thing I can think of is that either the table where you get the guid from is pointing to an other server/instance or the webservices is pointing to an other crm instance. If you're 100% sure that's the case then you could post a question in the crm forum.

      Delete
  4. This is an awesome post. I am trying to use SSIS to create campaign response type activities from an online application we have written. I can get everything to populate with the exception of the 'customer' field. I am not sure how I need to reference this. See below for a sample of my code.
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
    // Create a Entity object of type 'account'
    Entity newCampaignResponse = new Entity("campaignresponse");
    EntityReference CampaignID = new EntityReference("campaign", Row.CrmCampaignId);

    newCampaignResponse["regardingobjectid"] = CampaignID;
    newCampaignResponse["subject"] = Row.TitleSt;
    newCampaignResponse["receivedon"] = Row.responsedatetime;
    newCampaignResponse["customer"] = Row.CrmContactId;

    OptionSetValue obChannel = new OptionSetValue();
    obChannel.Value = Row.ChannelTypeCode;
    newCampaignResponse.Attributes.Add("channeltypecode", (OptionSetValue)obChannel);

    OptionSetValue obResponse = new OptionSetValue();
    obResponse.Value = Row.ResponseID;
    newCampaignResponse.Attributes.Add("responsecode", (OptionSetValue)obResponse);

    organizationservice.Create(newCampaignResponse);

    }

    Any ideas would be appreciated.

    ReplyDelete
    Replies
    1. If the customer field is a reference (GUID) to a contact then you need to use a EntityReference.

      Delete
  5. Great post! Very helpful. I'm slightly confused about setting the state and status in CRM 2013. We migrated from CRM 4.0 to CRM 2013 and I'm tasked with updating our ssis packages related to CRM. In the "old" packages script components, SetState is called to set the state and status of the entity using the 2007 api:

    //Update the state of the employee. This is active/inactive.
    SetState_employeeRequest state = new SetState_employeeRequest();
    if (Row.STATECODE == 0)
    {
    state.employeeState = employeeState.Active;
    state.employeeStatus = 1;
    }
    else
    {
    state.employeeState = employeeState.Inactive;
    state.employeeStatus = 2;
    }
    // EntityId is the GUID of the employee whose state is being changed.
    state.EntityId = new Guid(Row.GUID);
    // Execute the request.
    SetStateemployeeResponse stateSet = (SetStateemployeeResponse)CRMService.Execute(state);

    I find no reference to the SetState object in CRM 2013 sdk samples. How do I set these values in CRM 2013. Help is appreciated as I've been fighting this for days and am at the end of my rope!

    ReplyDelete
  6. Very helpful post. I've a question about the update. will I be able to do this?

    if (existingAccount["address1_city"] == 'New York')
    {
    //Do somthing
    }

    ReplyDelete
    Replies
    1. Not like that. In this example getting data from CRM is done via the OLEDB source on the CRM views and tables. However you can use that address1_city column from CRM as input column in the Script Component. Then you can use it.

      Delete
    2. Thanks. I'm talking about the update script where organization service is used to retrieve an existing account based on the accountid. I want to change certain fields based on the current value of those fields in CRM (the retrieved account). is this where early binding comes in picture?

      Delete
    3. no retrieving data should be done in the data flow itself.

      Delete
  7. Hello,
    The tutorial worked perfectly when working with the account entity. However, now I'm working with one of the custom entity and having issues.

    //new plan
    Entity plan = new Entity("new_plan");

    plan["new_plancardprogramid"] = (int) row["CardProgramID"];

    ....

    Guid planGUID = service.Create(plan);

    This is the error I'm getting.
    plan["new_plancardprogramid"]' threw an exception of type 'System.Collections.Generic.KeyNotFoundException'

    I'm getting same error for all the fields that I'm trying to set. I've double checked the name and the data type of each of the field. What am I doing wrong here? any guidance would be greatly appreciated. Thank you!

    ReplyDelete
    Replies
    1. Are you sure the name of the entity is "new_plan" instead of only "plan".
      Entity new_plan = new Entity("plan");

      And the same question for the field new_plancardprogramid...
      new_plan["plancardprogramid"] = (int) row["CardProgramID"];
      Guid planGUID = service.Create(new_plan);

      Also notice that the field names are case-sensitive...

      Delete
  8. Hi joost ,
    your blog is very helpful.....

    can I use insert and update crm records in one script component ,please tell me??

    ReplyDelete
    Replies
    1. That's no problem. You can do the conditional split in code (if statement)and then update or insert. I splitted the script in this example to keep things more clear.

      Delete
  9. Nice Example and Flow , Please share me the whole vstaProjects solution , It would helpful for me.

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...