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!