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!

Insert, update and delete records in CRM 2013 with SSIS - Part III: Add files as Annotation

Case
I want to upload files to CRM Annotations with SSIS. I already uploaded contacts, but now I also want to upload their documents. How do I do that?

Solution
You can use the same webservice in a Script Component that you used to insert/update records in CRM. For this example I have a source with a businessKey from my contact, a filename and a filepath. If your source doesn't contain the actual file in a blob, but only a filepath then you can use the Import Column Transformation.
I used a Lookup Transformation to get the identity id from a CRM database view because I need it to reference the document to my contacts.

Data Flow Example with Import Columns

1a) 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

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

2) DLL to SSIS machine
To use the assembly (DLL) from step 1a 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\


3) 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.



4) Script Component - Parameters
Add a Script Component (type destination) for adding the files. 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



5) Script Component - Input Columns
Add the columns that you need as Input Columns. For inserting the files as annotations you need the blob column containing the actual file, a filename, an entity ID to reference the file to a CRM contact and optionally a subject and/or description.

Input Columns for insert



6) 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

7) The Script - Insert
Here is an C# example (for VB.Net use this translator) for inserting files as annotations in CRM 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();

        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 new annotation object to store the properties
        Entity newAnnotation = new Entity("annotation");

        // Create an optionset to indicate to which entity this annotation will be linked
        OptionSetValue objectidtypecode = new OptionSetValue();
        objectidtypecode.Value = 2; // 2 is the enitity Contact in this case. Look it up in CRM.
        newAnnotation.Attributes.Add("objectidtypecode", (OptionSetValue)objectidtypecode);

        // Create an entity reference to contact and store it in the column ObjectId
        EntityReference Contact = new EntityReference("contact", Row.ContactId);
        newAnnotation["objectid"] = Contact;

        // Filename of the attachment (without path)
        newAnnotation["filename"] = Row.Filename;

        // The actual file is retrieved from the blob column as byte[]
        // And that byte[] is converted to a Base64String:
        newAnnotation["documentbody"] = Convert.ToBase64String(Row.Document.GetBlobData(0, Convert.ToInt32(Row.Document.Length)));

        // A subject with some title
        newAnnotation["subject"] = Row.Subject;

        // Add the annotation
        organizationservice.Create(newAnnotation);
    }
}

Note: this code example doesn't contain logging or exception handling!