SSIS 2008 Integration with CRM 2011 on-Premise

2012-6-6 | apurvghai | Dynamics CRM SDK | C#

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


  • 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(); 
                TextMessageEncodingBindingElement textMessageEncodingBindingElement = new  TextMessageEncodingBindingElement(MessageVersion.Soap12WSAddressing10, Encoding.UTF8);  
                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"?>
  <configSections>   </configSections>   <system.serviceModel>
        <binding name="CustomBinding_IOrganizationService">
          <!--    WsdlImporter encountered unrecognized policy assertions in ServiceDescription '':    -->
          <!--    <wsdl:binding name='CustomBinding_IOrganizationService'>    -->
          <!--    <ms-xrm:AuthenticationPolicy xmlns:ms-xrm="..">..</ms-xrm:AuthenticationPolicy>    -->
          <security defaultAlgorithmSuite="Default" authenticationMode="SspiNegotiated"
           requireDerivedKeys="true" securityHeaderLayout="Strict" includeTimestamp="true"
           keyEntropyMode="CombinedEntropy" messageProtectionOrder="SignBeforeEncryptAndEncryptSignature"
           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 />  
          <textMessageEncoding maxReadPoolSize="64" maxWritePoolSize="16"
           messageVersion="Default" writeEncoding="utf-8">
            <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
			 maxBytesPerRead="4096" maxNameTableCharCount="16384" />  
          <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" />
      <endpoint address="http://://XRMServices/2011/Organization.svc" binding="customBinding" bindingConfiguration="CustomBinding_IOrganizationService" contract="Crm.IOrganizationService"     name="CustomBinding_IOrganizationService">    
  </system.serviceModel>   <startup>
    <supportedRuntime version="v2.0.50727"/>  
  • 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();  
                public void StartRun()
                    serviceProxy = CrmHelper.GetCRMService("http://<servername>", "OrganizationName", "UserName", "Domain", "Password"); 
                    Entity account = new Entity(); 
                    account.LogicalName = "account"; 
                    account["name"] = "Apurv Ghai is the 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!


Recent Posts
Getting started with Dynamics 365 CRM SDK in C#
Jan 14, 19 | Dynamics CRM SDK
Using Postman with Dynamics 365 Online and OnPremise
Jan 14, 19 | Dynamics CRM SDK
Getting Started with WebAPI
Dec 28, 18 | Dynamics CRM SDK