Saturday, February 11, 2012

Integrating CRM 2011 using SQL Integration Services (SSIS)

I read this article and decided to make something similar for CRM 2011. You can see results in this post.



Datasource
As a datasource for SSIS I used table in Ms SQL 2008 server. Creation script:

CREATE TABLE [dbo].[Contact](
[FirstName] [varchar](100) NOT NULL,
[LastName] [varchar](100) NOT NULL,
[Phone] [varchar](100) NOT NULL,
[Email] [varchar](100) NOT NULL
) ON [PRIMARY]


Proxy assembly

Sql Integration Services 2008 support libraries with target versions of .Net Framework 2.0, 3.0 and 3.5 so it is impossible to use SDK assemblies (which have 4.0 version of .Net Framework). Because of this reason it would be required to use Service reference instead of usual referencing of SDK assemblies.

Open Visual Studio 2008 or 2010, choose new project, select .Net Framework 3.5 as target framework and type of project as Class Library:



Click with right of mouse on References and choose "Add Service Reference":



Input reference to endpoint of your CRM application, fill namespace and click OK:



Rename Class1 which was created by default for the project to CrmHelper. This class would be responsible for instantiation of OrganizationService:



Put the code which instantiates OrganizationServiceClient to CrmHelper class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CrmProxy.Crm;
using System.ServiceModel.Channels;
using System.ServiceModel.Security.Tokens;
using System.ServiceModel;

namespace CrmProxy
{
public class CrmHelper
{
public static IOrganizationService GetCRMService(string ServerHost, string OrgName, string UserName, string Domain, string Pwd)
{
Uri organizationUri = new Uri(string.Format("{0}/{1}/XRMServices/2011/Organization.svc", ServerHost, OrgName));

SymmetricSecurityBindingElement security = new SymmetricSecurityBindingElement();
security.ProtectionTokenParameters = new SspiSecurityTokenParameters();

HttpTransportBindingElement htbe = new HttpTransportBindingElement();
htbe.MaxReceivedMessageSize = 1000000000;

CustomBinding binding = new CustomBinding();
binding.Elements.Add(security);
TextMessageEncodingBindingElement tmebe = new TextMessageEncodingBindingElement(MessageVersion.Soap12WSAddressing10, Encoding.UTF8);
binding.Elements.Add(tmebe);
binding.Elements.Add(htbe);

EndpointAddress address = new EndpointAddress(organizationUri, EndpointIdentity.CreateUpnIdentity(string.Format("{0}@{1}", UserName, Domain)), new AddressHeader[] { });

OrganizationServiceClient osclient = new OrganizationServiceClient(binding, address);
osclient.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential(UserName, Pwd, Domain);

return (IOrganizationService)osclient;
}
}
}


Add to your project class which will contain extension methods for Entity proxy class (I called it Extensions):



Change the namespace of Extensions class to the namespace of Service Reference (like default namespace of project + name of your service reference - in my case CrmProxy.Crm):



Put instead of Extensions class declaration code which will extend methods/properties of Entity class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CrmProxy.Crm
{
partial class Entity
{
public Entity()
{
this.FormattedValuesField = new FormattedValueCollection();
this.RelatedEntitiesField = new RelatedEntityCollection();
}

public T GetAttributeValue<T>(string attributeLogicalName)
{
if (null == this.Attributes) { this.Attributes = new AttributeCollection(); };

object value;
if (this.Attributes.TryGetValue(attributeLogicalName, out value))
{
return (T)value;
}

return default(T);
}

public object this[string attributeName]
{
get
{
if (null == this.Attributes) { this.Attributes = new AttributeCollection(); };
return this.Attributes.GetItem(attributeName);
}

set
{
if (null == this.Attributes) { this.Attributes = new AttributeCollection(); };
this.Attributes.SetItem(attributeName, value);
}
}
}

public static class CollectionExtensions
{
public static TValue GetItem<TKey, TValue>(this IList<KeyValuePair<TKey, TValue>> collection, TKey key)
{
TValue value;
if (TryGetValue(collection, key, out value))
{
return value;
}

throw new System.Collections.Generic.KeyNotFoundException("Key = " + key);
}

public static void SetItem<TKey, TValue>(this IList<KeyValuePair<TKey, TValue>> collection, TKey key, TValue value)
{
int index;
if (TryGetIndex<TKey, TValue>(collection, key, out index))
{
collection.RemoveAt(index);
}

//If the value is an array, it needs to be converted into a List. This is due to how Silverlight serializes
//Arrays and IList<T> objects (they are both serialized with the same namespace). Any collection objects will
//already add the KnownType for IList<T>, which means that any parameters that are arrays cannot be added
//as a KnownType (or it will throw an exception).
Array array = value as Array;
if (null != array)
{
Type listType = typeof(List<>).GetGenericTypeDefinition().MakeGenericType(array.GetType().GetElementType());
object list = Activator.CreateInstance(listType, array);
try
{
value = (TValue)list;
}
catch (InvalidCastException)
{
//Don't do the conversion because the types are not compatible
}
}

collection.Add(new KeyValuePair<TKey, TValue>(key, value));
}

public static bool ContainsKey<TKey, TValue>(this IList<KeyValuePair<TKey, TValue>> collection, TKey key)
{
int index;
return TryGetIndex<TKey, TValue>(collection, key, out index);
}

public static bool TryGetValue<TKey, TValue>(this IList<KeyValuePair<TKey, TValue>> collection, TKey key, out TValue value)
{
int index;
if (TryGetIndex<TKey, TValue>(collection, key, out index))
{
value = collection[index].Value;
return true;
}

value = default(TValue);
return false;
}

private static bool TryGetIndex<TKey, TValue>(IList<KeyValuePair<TKey, TValue>> collection, TKey key, out int index)
{
if (null == collection || null == key)
{
index = -1;
return false;
}

index = -1;
for (int i = 0; i < collection.Count; i++)
{
if (key.Equals(collection[i].Key))
{
index = i;
return true;
}
}

return false;
}
}
}


Sign this assembly because it will be put to GAC:





Build and install this assembly to GAC using drag-drop to C:\Windows\Assembly folder ot gacutil:



SSIS Package

Start new SSIS Project:



Drag-drop Data Flow Task to Control Flow Pane:



Create new Connection to SQL DB which will be used for datasource:








Open Data Flow tab of project and drag and drop Ole DB Source:



Open and configure it:





Drag and Drop Script component to Data Flow tab, choose Transformation type, connect output of Ole DB Source and Script component:




Open script component, choose columns that should be transferred inside it and click Edit Script button to edit script:





In newly opened Visual Studio click with right of mouse on project and choose Properties to change Target Framework of project:




Add reference to assembly that was created step before and System.Runtime.Serialization:



Open main.sc and make following changes:

/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using CrmProxy;
using CrmProxy.Crm;

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

public override void PreExecute()
{
_organizationservice = CrmHelper.GetCRMService("http://crm2011", "Contoso", "administrator", "Contoso", "pass@word1");

base.PreExecute();
}

public override void PostExecute()
{
base.PostExecute();
/*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Entity newcontact = new Entity();
newcontact.LogicalName = "contact";
newcontact["firstname"] = Row.FirstName;
newcontact["lastname"] = Row.LastName;
newcontact["telephone1"] = Row.Phone;
newcontact["emailaddress1"] = Row.Email;

_organizationservice.Create(newcontact);
}

}


Save, rebuild and close Visual Studio with script.

That's it and everything is ready to run package.

Source table:


Package processing:


Imported data in CRM: