SSIS 2008 Integration with CRM 2011 on-Premise

This article deals with integrating SSIS 2008 and CRM 2011. You might have faced a lot of issues doing this part. As we know that SSIS 2008 still doesn’t support .NET 4.0 Framework so adding .NET 4 compiled assemblies is not a possible task/solution. What’s next? We can do this with backward compatibility using CRM v4 SDK. But with this approach we lose on CRM 2011 newer functions. After doing a lot of research I was finally able to write some code over it using .NET Framework 3.5

Before we get started, please refer the requirements:

  • Visual Studio 2010
  • WCF concepts
  • Hands on CRM SDK 2011

Note: I will not cover the SSIS Project creation, for more details you can see https://msdn.microsoft.com/en-us/library/ms137823.aspx.

Walkthrough

  • Create an empty solution in VS 2010
  • Right click, to add new Class Library Project
  • Choose the Target Framework as .NET 3.5
  • Name the project as “CrmProxy”
  • Create a class called “CrmHelper.cs”
  • Add service reference to Organization.svc and name it as “Crm”
  • Add the following code:

namespace CrmProxy
{
    public class CrmHelper
    {
        public static IOrganizationService GetCRMService(string ServerHost, string OrgName, string UserName, string Domain, string Pwd)
        {
            Uri OrgUri = new Uri(string.Format("{0}/{1}/XRMServices/2011/Organization.svc", ServerHost, OrgName));
            SymmetricSecurityBindingElement symmetricSecurityBindingElement = new SymmetricSecurityBindingElement();
            symmetricSecurityBindingElement.ProtectionTokenParameters = new SspiSecurityTokenParameters();
            HttpTransportBindingElement httpTransportBindingElement = new HttpTransportBindingElement();
            httpTransportBindingElement.MaxReceivedMessageSize = 1000000000;
            CustomBinding customBinding = new CustomBinding();
            customBinding.Elements.Add(symmetricSecurityBindingElement);
            TextMessageEncodingBindingElement textMessageEncodingBindingElement = new                 TextMessageEncodingBindingElement(MessageVersion.Soap12WSAddressing10, Encoding.UTF8);
            customBinding.Elements.Add(textMessageEncodingBindingElement);
            customBinding.Elements.Add(httpTransportBindingElement);
            EndpointAddress endpointAddress = new EndpointAddress(OrgUri);
            OrganizationServiceClient organizationServiceClient = new OrganizationServiceClient(customBinding, endpointAddress);
            organizationServiceClient.ClientCredentials.Windows.ClientCredential = new NetworkCredential(UserName, Pwd, Domain);
            return (IOrganizationService)organizationServiceClient;
        }
    }
}

  • Above code is the WCF implementation by creating Service client.
  • You will need to create extensions class for the same
  • Add new class “Extensions.cs” and write the relevant code for it.
  • Compile the code
  • You will notice a file in your project called “app.config”
  • We would need to add the config details as below

<?xml version="1.0"?>
<configuration>
  <configSections>
  </configSections>
  <system.serviceModel>
    <bindings>
      <customBinding>
        <binding name="CustomBinding_IOrganizationService">
          <!--    WsdlImporter encountered unrecognized policy assertions in ServiceDescription 'https://schemas.microsoft.com/xrm/2011/Contracts/Services':    -->
          <!--    <wsdl:binding name='CustomBinding_IOrganizationService'>    -->
          <!--        <ms-xrm:AuthenticationPolicy xmlns:ms-xrm="https://schemas.microsoft.com/xrm/2011/Contracts/Services">..</ms-xrm:AuthenticationPolicy>    -->
          <security defaultAlgorithmSuite="Default" authenticationMode="SspiNegotiated"
              requireDerivedKeys="true" securityHeaderLayout="Strict" includeTimestamp="true"
              keyEntropyMode="CombinedEntropy" messageProtectionOrder="SignBeforeEncryptAndEncryptSignature"
              messageSecurityVersion="WSSecurity11WSTrustFebruary2005WSSecureConversationFebruary2005WSSecurityPolicy11BasicSecurityProfile10"
              requireSecurityContextCancellation="true" requireSignatureConfirmation="false">
            <localClientSettings cacheCookies="true" detectReplays="true"
                replayCacheSize="900000" maxClockSkew="00:05:00" maxCookieCachingTime="Infinite"
                replayWindow="00:05:00" sessionKeyRenewalInterval="10:00:00"
                sessionKeyRolloverInterval="00:05:00" reconnectTransportOnFailure="true"
                timestampValidityDuration="00:05:00" cookieRenewalThresholdPercentage="60" />
            <localServiceSettings detectReplays="true" issuedCookieLifetime="10:00:00"
                maxStatefulNegotiations="128" replayCacheSize="900000" maxClockSkew="00:05:00"
                negotiationTimeout="00:01:00" replayWindow="00:05:00" inactivityTimeout="00:02:00"
                sessionKeyRenewalInterval="15:00:00" sessionKeyRolloverInterval="00:05:00"
                reconnectTransportOnFailure="true" maxPendingSessions="128"
                maxCachedCookies="1000" timestampValidityDuration="00:05:00" />
            <secureConversationBootstrap />
          </security>
          <textMessageEncoding maxReadPoolSize="64" maxWritePoolSize="16"
              messageVersion="Default" writeEncoding="utf-8">
            <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
                maxBytesPerRead="4096" maxNameTableCharCount="16384" />
          </textMessageEncoding>
          <httpTransport manualAddressing="false" maxBufferPoolSize="524288"
              maxReceivedMessageSize="65536" allowCookies="false" authenticationScheme="Anonymous"
              bypassProxyOnLocal="false" decompressionEnabled="true" hostNameComparisonMode="StrongWildcard"
              keepAliveEnabled="true" maxBufferSize="65536" proxyAuthenticationScheme="Anonymous"
              realm="" transferMode="Buffered" unsafeConnectionNtlmAuthentication="false"
              useDefaultWebProxy="true" />
        </binding>
      </customBinding>
    </bindings>
    <client>
      <endpoint address="https://://XRMServices/2011/Organization.svc"
          binding="customBinding" bindingConfiguration="CustomBinding_IOrganizationService"
          contract="Crm.IOrganizationService" name="CustomBinding_IOrganizationService">
       </endpoint>
    </client>
  </system.serviceModel>
  <startup>
    <supportedRuntime version="v2.0.50727"/>
  </startup>
</configuration>

  • Now add new project to this solution
  • This time use, Console Application and Provide name as CrmProxyTester
  • Add the reference of the CrmProxy.dll
  • Write the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CrmProxy;
using CrmProxy.Crm;
namespace CrmProxyTester
{
    class Program
    {
        IOrganizationService serviceProxy;
        static void Main(string[] args)
        {
            Program programClassInstance = new Program();
            programClassInstance.StartRun();
        }
        public void StartRun()
        {
            serviceProxy = CrmHelper.GetCRMService("https://<servername>", "OrganizationName", "UserName", "Domain", "Password");
            Entity account = new Entity();
            account.LogicalName = "account";
            account["name"] = "Apurv Ghai is the account";
            serviceProxy.Create(account);         }
    }
}

  • In above code I’m connecting to CrmProxy and creating an account in CRM
  • Now, we’re all set.
  • Build the complete solution and run your command application.

This completes the testing the project with .NET 3.5 class library. You can use the same in your SSIS project.

I have attached the code for download.

Happy Integration!

Cheers,
Apurv

 

CrmProxy.zip