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: