Integration

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:

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:

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:

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:

13 Comments on “Integrating CRM 2011 using SQL Integration Services 2012

  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?

  2. Hello,

    If I'm not wrong – try to put mentioned assembly to folders
    C:Program Files (x86)Microsoft SQL Server100DTSBinn
    C:Program FilesMicrosoft SQL Server100DTSBinn

    Kind regards,
    Andrii.

  3. 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 Server110DTSBinn

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

  4. Hello Andrii,

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

    Thanks

  5. Hi,

    I tried this code but somehow I’m getting following error.
    ———————————-
    ‘.’, hexadecimal value 0x00, is an invalid character. Line 68, position 249.
    ———————————-
    Any suggestions for this?

        1. Tejas,
          You should recheck Reference file in which generated client for CRM Endpoint is stored. You will not see it in VS. Open project folder with “File Explorer” and look for Reference.cs – it should contain issue you mentioned.
          Andrii

Leave a Reply

Your email address will not be published. Required fields are marked *