Tuesday, November 13, 2012

Integrating CRM 2011 using SQL Integration Services 2012

I have already wrote similar article using SSIS 2008. Following article describes how to use the same functionality using new version of SSIS.

Import of data to CRM

Datasource
As a data source I will use table of Microsoft SQL Server 2012 (but it could be whatever you want – Excel, CSV, Oracle DB e.t.c.). Here is the script to create source table:
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]



SSIS Package

Create new SSIS Project:


Add new DataFlow Task to package:

Open DataFlow task and add new OLE DB Source:


Configure Connection for Source:






Recheck that it is possible to retrieve data from datasource with preview button:



Add Script Component to your dataflow and link DataSource and newly added Script Component:



Open Script Component and configure Input Columns:

Open Script tab and click Edit Script button:

New Instance of Visual Studio 2012 will be opened and you should see something similar to:

Add references to CRM SDK assemblies, System.Runtime.Serialization and System.ServiceModel:


References list should look like:
Modify main.cs to following:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using System.ServiceModel.Description;

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

    public override void PreExecute()
    {
        base.PreExecute();

        ClientCredentials credentials = new ClientCredentials();
        credentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;
        organizationservice = new OrganizationServiceProxy(
            new Uri("http://crm/contoso/XRMServices/2011/Organization.svc"), null, credentials, null);
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Entity newcontact = new Entity("contact");

        if (!Row.FirstName_IsNull)
            newcontact["firstname"] = Row.FirstName;

        if (!Row.LastName_IsNull)
            newcontact["lastname"] = Row.LastName;

        if (!Row.Email_IsNull)
            newcontact["emailaddress1"] = Row.Email;

        if (!Row.Phone_IsNull)
            newcontact["telephone1"] = Row.Phone;

        organizationservice.Create(newcontact);
    }

}

Save and close VS, save Script Component and run package:

Results in CRM:

Usage of CRM as a datasource


In my experience I had situations when CRM Data was used as a datasource. Following part of article describes how to use CRM Data as a datasourse. Of course for OnPremice it is possible to use SQL to access data but the same approach would not work for Partnet hosted CRM or CRM Online.

Create new package, new DataFlow Task and add new Script Component with Source Type:

Open Script Component Properties, Inputs and Outputs tab, modify outputs:




Open Script Tab and click Edit Script button:

In new Instance of Visual Studio add references to CRM SDK assemblies, System.Runtime.Serialization and System.ServiceModel, modify CreateNewOutputRows method:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.Xrm.Sdk;
using System.ServiceModel.Description;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;

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

    public override void PreExecute()
    {
        base.PreExecute();

        ClientCredentials credentials = new ClientCredentials();
        credentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;

        organizationservice = new OrganizationServiceProxy(
            new Uri("http://crm/contoso/XrmServices/2011/Organization.svc"), null, credentials, null);
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void CreateNewOutputRows()
    {
        QueryExpression query = new QueryExpression("contact")
        {
            ColumnSet = new ColumnSet(new string[] { "fullname", "createdon", "emailaddress1" }),
            PageInfo = new PagingInfo()
            {
                Count = 250,
                PageNumber = 1,
                ReturnTotalRecordCount = false
            }
        };

        EntityCollection results = null;

        do
        {
            results = organizationservice.RetrieveMultiple(query);

            foreach (Entity record in results.Entities)
            {
                ContactBuffer.AddRow();

                ContactBuffer.ContactId = record.Id;
                ContactBuffer.CreatedOn = record.GetAttributeValue<DateTime>("createdon");

                if (record.Contains("fullname"))
                    ContactBuffer.FullName = record.GetAttributeValue<string>("fullname");

                if (record.Contains("emailaddress1"))
                    ContactBuffer.Email = record.GetAttributeValue<string>("emailaddress1");
            }
        }
        while (results.MoreRecords);
    }

}


Save and close Visual Studio, add destination to your DataFlow (in my case I used text file but you can use any type of datadestination you want):

Run package and check results:

8 comments:

  1. Hi Andrii. Have tried to follow your instructions with CRM 2011 as a datasource and a text file as destination, but when I run the package I'm getting:

    Script Component has encountered an exception in user code:
    Project name: SC_1ced....
    Could not load file or assembly 'Microsoft.Xrm.Sdk, version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies.
    The system cannot find the file specified.

    Any idea what I might be doing wrong?

    ReplyDelete
    Replies
    1. Hello,

      If I'm not wrong - try to put mentioned assembly to folders
      C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn
      C:\Program Files\Microsoft SQL Server\100\DTS\Binn

      Kind regards,
      Andrii.

      Delete
    2. Hi Andrii. Done that but still getting the same error.

      Delete
  2. Andrii,

    Thank you so much for setting this out so clearly - it is just what I needed.
    Neil I got the same error as you and fixed it by putting the dlls in
    C:\Program Files(x86)\Microsoft SQL Server\110\DTS\Binn

    note: that got me past that error but then I had a further unknown error which I overcame by specifying a specific valid username in credentials

    ClientCredentials credentials = new ClientCredentials();
    credentials.UserName.UserName = "your username";
    credentials.UserName.Password = "your password";

    ReplyDelete
  3. Hello Andrii,

    Is there any way to suppress duplicates using this - referencing duplicate detection rules already established in CRM?

    Thanks

    ReplyDelete
    Replies
    1. Hello,

      This is possible. Recheck following article - http://msdn.microsoft.com/en-us/library/hh210213.aspx

      Kind regards,
      Andrii.

      Delete
  4. Thanks Andrii - that code looks a bit scary but I will give it a go.

    ReplyDelete