SSIS 2008 Integration with CRM 2011 on-Premise

Artikel
06/06/2012

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">
          
          
          
          <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

Comments

Anonymous
November 09, 2013
Thanks for posting this solution.Here is the problem "serviceProxy.Execute" funcation is taking "OrganizationRequest" object as parameter but I want to do something like asserviceProxy.Execute(new SetStateRequest { EntityMoniker = new EntityReference("workflow", w), State = OptionSetValue(wfStateCode), Status = OptionSetValue(wfStatusCode) });Can you explain me How I can create "OrganizationRequest" from the above code? or How I can pass "SetStateRequest" parameter.Appreciate your help.
Anonymous
December 02, 2013
How use CrmProxyTester for assignrequest??

Freigeben über

SSIS 2008 Integration with CRM 2011 on-Premise

Before we get started, please refer the requirements:

Walkthrough

Comments

Zusätzliche Ressourcen