Release: First Service Pack of SSIS Integration Toolkit

Following our release of SSIS Integration Toolkit for Microsoft Dynamics CRM, we have received a warm welcome by the community. Thanks everyone.

Today, we have released our first service pack for the toolkit which includes the following bug fixes.

• Fixed: CRM destination component throws error when writing data to currency fields, if SOAP 2011 endpoint is used (Thanks Les for reporting this issue)
• Fixed: When CRM Source Component Editor is re-opened, the SSIS metadata of CRM fields are always repopulated. If you click OK to dismiss the window without making any changes to the source component, it invalidates the mappings for all the downstream components
• Fixed: CRM destination component does not handle writing activityparty fields properly for SOAP 2011 endpoint
• Fixed: You might experience expired token error if your data flow task runs for hours, when SOAP 2011 endpoint is used for CRM Online or IFD deployment

A new build is now available for download at our product download page.

In order to install the new build, you will need to uninstall the previous version first.

Thanks for reading. 

CRM2011: Get the Right Server URL (Silverlight Version)

After my previous post about how to get the right server URL in CRM JavaScript code, I was asked to provide a translation of the code to C# so that it can be used in Silverlight web resource. So here is post along with the translated code.

public static string GetServerUrl()
{
    var context = GetContext();
    var isOutlookClient = (bool)context.Invoke("isOutlookClient");
    var isOutlookOnline = (bool)context.Invoke("isOutlookOnline");

    var documentUri = HtmlPage.Document.DocumentUri;

    var localServerUrl = string.Format("{0}://{1}:{2}", documentUri.Scheme, documentUri.Host, documentUri.Port);
    if (isOutlookClient && !isOutlookOnline)
    {
        return localServerUrl;
    }

    var baseServerUrl = (string)context.Invoke("getServerUrl");
    var serverUrl = Regex.Replace(baseServerUrl, @"^(http|https):\/\/([_a-zA-Z0-9\-\.]+)(:([0-9]{1,5}))?", localServerUrl);
    return Regex.Replace(serverUrl, @"\/$", string.Empty);
}

// This is the same code that comes from CRM SDK in its SoapForSilverlightSample project.
private static ScriptObject GetContext()
{
    var xrmProperty = (ScriptObject)HtmlPage.Window.GetProperty("Xrm");
    if (null == xrmProperty)
    {
        //It may be that the global context should be used
        try
        {
            var globalContext = (ScriptObject)HtmlPage.Window.Invoke("GetGlobalContext");

            return globalContext;
        }
        catch (InvalidOperationException)
        {
            throw new InvalidOperationException("Property \"Xrm\" is null and the Global Context is not available.");
        }
    }

    var pageProperty = (ScriptObject)xrmProperty.GetProperty("Page");
    if (null == pageProperty)
    {
        throw new InvalidOperationException("Property \"Xrm.Page\" is null");
    }

    var contextProperty = (ScriptObject)pageProperty.GetProperty("context");
    if (null == contextProperty)
    {
        throw new InvalidOperationException("Property \"Xrm.Page.context\" is null");
    }

    return contextProperty;
}

If you are talking to CRM SOAP endpoint, you may get the complete URL using the following code.

Uri serviceUrl = new Uri(GetServerUrl() + "/XRMServices/2011/Organization.svc/web");

If you need to talk to CRM REST endpoint, you may get the complete URL using this one.

Uri serviceUrl = new Uri(GetServerUrl() + "/XRMServices/2011/OrganizationData.svc");

Hope this helps.

Improve CRM Data Load Performance by using BDD in SSIS

[DISCLAIMER] This blog post mainly talks about the benefits of our commercial offering, but the practice should be generally applicable to other approaches or components that you might be currently using in SSIS. It is not my intention to turn my blog into a commercial space, but I do believe this blog post would help even your are using something different, assuming that SSIS is the tool for your data integration purpose. [/DISCLAIMER]


If you have ever been engaged in any Microsoft Dynamics CRM data integration project, I am relatively sure that you have invested time and resources to tune your data integration component to its maximum possible performance so that it takes the least time to finish the data integration tasks.

This blog post shows you how to load one million records into Microsoft Dynamics CRM 2011 on-premise installation with a two-hour time range, using our product - SSIS Integration Toolkit for Microsoft Dynamics CRM, by taking advantage of the Balanced Data Distributor (BDD) component that Microsoft released to public community that works for SQL Server Integration Services (SSIS).

In case you don't know BDD component, here is a little background information about the component. BDD is a data flow transformation component that takes a single input and evenly distributes the incoming rows to one or more outputs uniformly via multithreading. The purpose of BDD component is to maximize the output performance of ETL data flow tasks. BDD can be used when your downstream pipeline component (say the destination component) is the bottleneck of the entire data flow task.

When working with Microsoft Dynamics CRM data integration, we have a perfect reason to use BDD, mainly because writing data into CRM is slow due to the nature of web service interface. In other words, In most of cases, you would find that the CRM destination component which writes data into CRM is the bottleneck of your data flow tasks. Using BDD, we can distribute incoming rows from upstream pipeline components and split them into multiple CRM destination components, so they write data into CRM simultaneously and concurrently by taking advantage of the multi-threading capability of SSIS engine.

To demonstrate the benefits of using BDD component, I first tried a single CRM destination component in my data flow task without using BDD, so the data flow writes data into CRM using a single thread. It took me 5 hours, 48 minutes to finish the load of 1,000,000 record into CRM contact entity. Here is what the data flow task looks like.

The following screen shot shows how the data flow runs using dtexec command line.

Next, I tried to use BDD and split the input into 10 outputs so that we write to CRM contact entity using 10 concurrent threads. The data flow finishing loading 1,000,000 records within 2 hours, 3 minutes. Here is what the data flow task looks like.

The following screen shots shows how the data flow runs using dtexec command line.

The improvement is about 2.84 times, it's not surprising that it's not exactly 10 times faster. 

A few facts

1. This is not a scientific benchmark. 
2. My testing was conducted on a desktop computer of 4-year old which has everything installed in the single box. The following is the spec of the computer. 
   • Processor: Intel Core 2 Quad Q9550 @2.83GHz
   • Memory: 8GB PC2-6400 DDR2-SDRAM
   • Hard Disk: Seagate 7200RPM SATA 1.5Gb/s
   • Operating System: Windows 2008 R2 Server
   • Database Server: SQL Server 2008 R2
   • Microsoft Dynamics CRM Server 2011 with Rollup 6
   • SSIS Adapter: KingswaySoft SSIS Integration Toolkit for Microsoft Dynamics CRM
3. The testing was done in an on-premise environment, your data load performance would be different if you are using CRM online or partner-hosted environment. 
4. I have intentionally used 64-bit dtexec.exe with the hope that we can take advantage of SSIS 64-bit run-time. Controversy to what I believed, running it using 32-bit dtexec.exe is actually not slower, but 10% faster than 64-bit runtime. The reason is probably related to the cost associated with memory addressing in 64-bit runtime. 
5. My input data is very simple, it has only two fields, firstname and lastname. When you have more fields, you would expect the data load performance to degrade in certain scale. 
6. I was hoping to be able to load 1 million records into CRM within one hour after using BDD, but it still took me two hours. With a better IO system and more computer power, I am relatively positive that the goal (one million records within one hour) is achievable. 
7. The single-destinationed data flow task writes about 47.84 records to CRM server per second (54.27 records/s when 32-bit runtime is used), you may use this as a baseline rate if you want to compare yours with mine. 

Summary

• BDD improves the data load performance by taking advantage of the multi-threading capability of SSIS engine. 
• You should carefully choose a right number of the outputs for BDD component. It's not the case that the more the better. Depending on your servers' capacity (including processor, memory, IO system) and the network latency between your client system and CRM server, it could be 3, 5, 10, or something else for the maximized performance, which you may find out by running different tests. 
• There are many ways that you can use to improve the data load performance, BDD is just one of the easy ways that make the data load faster, which is the main topic that we are trying to cover in this blog post.  

If you are interested in any of the data flow tasks or sample data, please feel free to let me know, so that I can send you the SSIS package.

Thanks for reading.

CRM 2011: Get the Right Server URL in Your CRM Client Script

If you have worked with Microsoft Dynamics CRM 2011 long enough, you have most likely used getServerUrl function provided by the platform's API.

However, there is a problem with this function, it returns the base server URL which is the one registered in CRM deployment manager. When you work with a CRM form or web resource, you don't want to use the base server URL directly, instead you need the local server URL that hosts the current form or web resource. For instance, if the user is using a URL which is different from what's in deployment manager, say IP address or a domain alias, the URL will cause cross-domain calls, which will fail.

For this reason, I have baked a solution today which might be able to address this issue universally for all scenarios (hopefully).

var getServerUrl = function () {
    var context, crmServerUrl;
    if (typeof GetGlobalContext != "undefined") {
        context = GetGlobalContext();
    }
    else if (typeof Xrm != "undefined") {
        context = Xrm.Page.context;
    }
    else {
        throw new Error("CRM context is not available.");
    }

    if (context.isOutlookClient() && !context.isOutlookOnline()) {
        crmServerUrl = window.location.protocol + "//" + window.location.host;
    } else {
        crmServerUrl = context.getServerUrl();
        crmServerUrl = crmServerUrl.replace(/^(http|https):\/\/([_a-zA-Z0-9\-\.]+)(:([0-9]{1,5}))?/, window.location.protocol + "//" + window.location.host);
        crmServerUrl = crmServerUrl.replace(/\/$/, ""); // remove trailing slash if any
    }
    return crmServerUrl;
}; 

In the code snippet, I assumed that a server host name can only be made of English, numeric characters, _ (underscore), . (dot), - (dash).

If you are using Silverlight web resource, you need to translate the code to C#, which shouldn't be something difficult. [Update - Please refer to my another blog post for C# code that works for Silverlight]

Although I am bluntly positive that this could solve the problem universally, but there might be exceptional scenarios that I am not currently aware. If that's the case, please let me know by leaving a comment below, I will try to come up with something better. ;-)

Thanks for reading, hope this helps.

New Licensing Model and Pricing Structure of SSIS Integration Toolkit

Following my previous announcement of the availability of SSIS Integration Toolkit for Microsoft Dynamics CRM, we received quite some feedback about the offering. One of the feedbacks that we received was the licensing model was too complicated to understand and the price was a little too high.

For this reason, we have adjusted the licensing model and also updated pricing structure, so it is a lot simpler and cheaper than the previous one.

Under the new licensing model, we only have two product licenses, and one maintenance & support term.

SSIS Integration Toolkit for Microsoft Dynamics CRM, Perpetual License	$895.00
SSIS Integration Toolkit for Microsoft Dynamics CRM, One-year Subscription License	$495.00
SSIS Integration Toolkit for Microsoft Dynamics CRM, One-year Maintenance & Support	$395.00

A new build of the software has been uploaded to accommodate those changes, which also includes some important bug fixes since the announcement.

No license is required any more (not even a trial license) if you just need to create an SSIS package using the toolkit, or play with the toolkit within SQL Server Business Intelligence Development Studio. However, you should acquire a license if you want to deploy the SSIS packages to an environment where you want the packages to be executed on a scheduled basis.

Thanks for reading.

Config-less WCF Service Endpoint Binding for CRM 2011 On-Premise

I previously blogged about how to consume CRM 2011 WCF Services through adding service reference in Visual Studio. Sometimes, there might be a need that you want to initialize the service in your code without the config file.

So here is the method that does this job for CRM 2011 on-premise deployment.

private static CustomBinding GetServiceEndpointBinding(string bindingName)
{
     var endpointBinding = new CustomBinding
                               {
                                   Name = bindingName
                               };

     // Configure security binding
     var securityElement = SecurityBindingElement.CreateSspiNegotiationBindingElement(true);
     securityElement.DefaultAlgorithmSuite = SecurityAlgorithmSuite.Default;
     securityElement.KeyEntropyMode = SecurityKeyEntropyMode.CombinedEntropy;
     securityElement.MessageSecurityVersion = MessageSecurityVersion.WSSecurity11WSTrustFebruary2005WSSecureConversationFebruary2005WSSecurityPolicy11BasicSecurityProfile10;
     securityElement.MessageProtectionOrder = MessageProtectionOrder.SignBeforeEncryptAndEncryptSignature;
     securityElement.SecurityHeaderLayout = SecurityHeaderLayout.Strict;
     securityElement.IncludeTimestamp = true;

     var messageEncoding = new TextMessageEncodingBindingElement();
     messageEncoding.ReaderQuotas.MaxStringContentLength = int.MaxValue;
     messageEncoding.ReaderQuotas.MaxArrayLength = int.MaxValue;
     messageEncoding.ReaderQuotas.MaxBytesPerRead = int.MaxValue;

     var transport = new HttpTransportBindingElement
                              {
                                   MaxBufferSize = int.MaxValue,
                                   MaxReceivedMessageSize = int.MaxValue
                              };

     // Add the SymmetricSecurityBindingElement to the BindingElementCollection.
     endpointBinding.Elements.Add(securityElement);
     endpointBinding.Elements.Add(messageEncoding);
     endpointBinding.Elements.Add(transport);

     return endpointBinding;
}

To use the above method, you can wire it up like this.

 var endpoint = new EndpointAddress("http://CrmServerName/CrmOrgName/XRMServices/2011/Organization.svc");

 var endpointBinding = GetServiceEndpointBinding("CustomBinding_IOrganizationService");

 var organizationService = new OrganizationServiceClient(endpointBinding, endpoint);

 // Provide login credential, you should only need one of the following
 
 // If you are using integrated authentication
 organizationService.ChannelFactory.Credentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;

 // if you are using named CRM account
 // organizationService.ClientCredentials.Windows.ClientCredential = new NetworkCredential("UserName", "Password", "Domain");

This is a by-product of my SSIS Integration Toolkit development effort. As you may or may not appreciate, I took me quite some hours to get to the point that the code works for CRM on-premise deployment.

It should be noted that the above snippet works only for CRM on-premise deployment. CRM online or CRM federated deployments have vastly different WCF bindings from on-premise, which require quite some extra effort in order to be able to connect successfully.

Considering the majority of CRM installation is on-premise deployment, I hope this post helps some people in the community who have the same needs.