Data Integration/Migration using SQL Integration Services (SSIS) 2008

Today’s guest blogger is CRM MVP Darren Liu who is a CRM specialist at Sonoma Partners in Chicago.
I wrote a blog article last year on how to integrate Microsoft Dynamics CRM using SQL Integration Service (SSIS) 2005 (http://tiny.cc/BQiiR) . I hope that article provided you with an alternative solution for your integration/data migration project with CRM. Due to the limited capabilities in SSIS 2005 with CRM web services, we created a proxy class as a work around to provide easy access to the CRM API.
With the new release of SQL server 2008, there were few improvements that simplify the integration of SSIS and CRM. It does not require you to create the proxy class anymore since SSIS 2008 allows you to add web references within the script component object. It also allows you to code in the language that I like the most, C#.
Here I would like to share how to leverage SSIS 2008 to integrate with CRM without the proxy class so that you can use it on your next CRM data integration/migration project.
Before we get started, here’s the list of requirements:

• SQL Server 2008 Standard/Enterprise Edition with SQL Integration Service Installed
• Microsoft Dynamics CRM 4.0
• Visual Studio 2008 Professional Edition SP 1 with Business Intelligence Tools Installed
• CRM SDK and C# knowledge

In this blog, I will use an example to show you how to send contact data stored in an Excel document to MSCRM 4.0 via CRM Web Services using SSIS.
Source Data
Source data is data from the other system that you would like to send to the CRM system. Your source data can be a text file, a database, etc… Since we often use Excel to collect our information, I will use a simple Excel document as my source data for this blog article.
Source Data: Excel Spread Sheet


Create SSIS Package
Launch Visual Studio 2008 to start a New Integration Services Project
After creating the project, follow the steps below to set up the SSIS package.

- Rename Package.dtsx to Contact.dtsx

Add Control Flow Items
Drag and drop a “Data Flow Task” from the Toolbox to the Control Flow Design Pane.

Add Data Flow Items
Double click on the Data Flow Task item that you just added and it will take you to the Data Flow Design Pane. Here we will specify the source data and also to write script to send data to CRM.
Specify Source Data

- Since our source data is an Excel document, drag and drop the Excel Source from the Toolbox to the design pane.
- Double click Excel Source to open the Excel Source Editor.
- Click New… button to open the Excel Connection Manager to specify the Excel file path, and then click OK.

- Select “Table or View” from Data access mode dropdown box.
- Select “Sheet1$” from Name of the Excel sheet dropdown box.
- Click OK to close the Excel Source Editor window.

Setup Script Component

- Drag and Drop Script Component to the design pane.
- Select Transformation and then click OK.
- Connect the two shapes by dragging the green arrow from Excel Source to Script Component.
- Double click the script component to open up the Script Transformation Editor.
- Select the columns that you would like to send to MSCRM from the Input Column window. In this example, I selected First Name, Last Name, Phone and Email Address.
- Remove Output in the Inputs and Outputs section since we are not going to output anything in this example.
- Click on the Script tab, click on Edit Script button. The Visual Studio window should open.

Add CRM Services
Since SSIS 2008 allows you to add web references in the Script Component, we will add the two web service references in this step. Please be aware that you must save the script component project by clicking the Save button on the toolbar after you added the CRM web references, otherwise the web references will not load next time you reopen the script component.
- Right click on the project in the Project Explorer window.
- Select Add Web Reference… from the menu.

- Repeat the steps above to add the CRM metadata service if necessary.
Coding the Package
In order to use the web reference in our code, we need to include the CrmSdk web reference to the script component project. To get the script component namespace, right click on the project and select Properties… from the menu. The namespace is in the Default namespace textbox. In this example, my script component name space is SC_ad0e4b91cb7e48cdb8fa2d240e3e5c30.csproj.

I added the following statement to my project.

using SC_ad0e4b91cb7e48cdb8fa2d240e3e5c30.csproj.CrmSdk;

Lastly, copy and paste the following code to the ScriptMain section:

private CrmService service = null;

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

        CrmAuthenticationToken token = new CrmAuthenticationToken();
        token.AuthenticationType = 0;
        token.OrganizationName = "AdventureWorkCycles";

        service = new CrmService();
        service.Url = "http://localhost/mscrmservices/2007/crmservice.asmx";
        service.CrmAuthenticationTokenValue = token;
        service.Credentials = System.Net.CredentialCache.DefaultCredentials;
    }

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

    public override void ContactInput_ProcessInputRow(ContactInputBuffer Row)
    {
        contact cont = new contact();

        if (!Row.FirstName_IsNull)
        {
            cont.firstname = Row.FirstName;
        }

        if (!Row.LastName_IsNull)
        {
            cont.lastname = Row.LastName;
        }

        if (!Row.Phone_IsNull)
        {
            cont.telephone1 = Row.Phone;
        }

        if (!Row.Email_IsNull)
        {
            cont.emailaddress1 = Row.Email;
        }

        service.Create(cont);
    }

Execute the SSIS package
After coding the SSIS package, right-click on the Contact.dtsx package and then select Execute Package. After the package has executed successfully, you should see the records in CRM.

Deploy the SSIS Package
After successfully testing the package, deploying the package is pretty easy. It requires the same steps as the previous version of SSIS. I have included the steps again below.

- Right-click on the CRM 4.0 SSIS project and then select Properties.
- Click on the Deployment Utility tab and set the Create Deployment Utility property to True.

- Recompile the CRM 4.0 SSIS project. You should see CRM 4.0 SSIS.SSISDeploymentManifest in the bin\Deployment folder.
- Double-click on the manifest file and follow the wizard to deploy the SSIS package to your SQL server.

Summary
That’s all there is to it! Hopefully you have gotten the idea of how to use the latest version of SSIS to send data to CRM. SSIS 2008 has a lot of improvements to make our jobs easier to integrate systems. In this sample, I only demonstrated how to import records in CRM. In an actual data integration or migration implementation, we still have a lot more to consider such as updating, deleting and error handling. This is one of the many approaches that you can use to integrate/migrate data with CRM. I hope this will help you in your next CRM project.

Cheers,
Darren Liu

Posted in: CRM 2011 Integration,CRM 4.0 - CRM 2011 Migration