Insert, update and delete records in CRM 2013 with SSIS - Part II: Inactivate records

Case
A while a go I did a post on how to insert, update or delete records in CRM (2013), but what if you want to (de)activate records instead of deleting them to preserve some history in CRM?

Solution
This is a continuation of a previous blog post. Please read it to get more inside details.

1) Start
To deactivate or activate records in CRM, you need the identity id (GUID) of that record. In SSIS you could full outer join your source with a CRM identity view and if the records exists in CRM, but not in your source then you can deactivate the record.


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 the Microsoft.Xrm.Sdk.dll and Microsoft.Crm.Sdk.Proxy.dll assemblies 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 assemblies (DLL) from step 2a in SSIS, you need to add the DLL files 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 them 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 the deactivate. 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 deactivation you only need the entity ID from CRM. This is the Technical Id (a guid) from the CRM entity that you want to update.


Input Columns for deativate

7) The Script - Add assembly
Hit the Edit Script button to start the VSTA editor. In the solution explorer you need to add four references (microsoft.crm.sdk.proxy.dll is for inactivation):

• microsoft.xrm.sdk.dll (from the SSIS bin folder mentioned in step 3, use browse)
• microsoft.crm.sdk.proxy.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 - Deactivate
Here is an C# example (for VB.Net use this translator) for deactivating an existing CRM account with SSIS 2012.

// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.Xrm.Sdk;                // Added
using Microsoft.Xrm.Sdk.Client;         // Added
using Microsoft.Crm.Sdk.Messages;       // 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 CRM request to (de)activate record
        SetStateRequest setStateRequest = new SetStateRequest();

        // Which entity/record should be (de)activate?
        // First part in the entityname, second
        // is the entity id from the CRM source.
        setStateRequest.EntityMoniker = new EntityReference("account", Row.myGuid);

        // Setting 'State' (0 – Active ; 1 – InActive)
        setStateRequest.State = new OptionSetValue(1);

        // Setting 'Status' (1 – Active ; 2 – InActive)
        setStateRequest.Status = new OptionSetValue(2);

        // Execute the request
        SetStateResponse response = (SetStateResponse)organizationservice.Execute(setStateRequest);
    }
}

8b) The Script - Adding an inactive account
And you can also combine adding and deactivating. Here is an C# example (for VB.Net use this translator) for inserting an inactive account with SSIS 2012.

// C# Code
// 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");
    
    // fill crm fields. Note fieldnames are case sensitive!
    newAccount["name"] = Row.AccountName;
    newAccount["emailaddress1"] = Row.Email;
    newAccount["telephone1"] = Row.Phone;

    // Create account and store its Entity ID to deactivate the account
    Guid AccountGuid = organizationservice.Create(newAccount);

    // Create CRM request to (de)activate record
    SetStateRequest setStateRequest = new SetStateRequest();

    // Which entity/record should be (de)activate?
    // First part in the entityname, second
    // is the entity id you got from adding
    // the new account.
    setStateRequest.EntityMoniker = new EntityReference("account", AccountGuid);

    // Setting 'State' (0 – Active ; 1 – InActive)
    setStateRequest.State = new OptionSetValue(1);

    // Setting 'Status' (1 – Active ; 2 – InActive)
    setStateRequest.Status = new OptionSetValue(2);

    // Execute the request
    SetStateResponse response = (SetStateResponse)organizationservice.Execute(setStateRequest);
}

Note: these code examples don't contain logging or exception handling!