Blog - KingswaySoft

Writing Script Component or Script Task using CRM Connection Manager

24 June 2013

Daniel Cai

Along with our v3.1 release of SSIS Integration Toolkit for Microsoft Dynamics CRM, we added a new capability that you can now write SSIS script component or script task by utilizing CRM connection managers in your SSIS package. This provides a great extensibility so that you can utilize full CRM SDK capability to talk to your CRM server so that you can achieve something that's not possible with the toolkit out-of-the-box (which should be some very special scenario). In this blog post, I will walk you through how you can do that using SSIS 2008 R2.

Note: The procedures below are based on SSIS 2008 R2 using C# programming language. If you are using other version of SQL Server, the procedures might be slightly different.

Preparation

In order to be able to work with CRM connection managers in SSIS Script Component or Script Task, you need to first copy KingswaySoft.DynamicsCrmServices.dll file from GAC to C:\Windows\Microsoft.NET\Framework\v2.0.50727 (SQL Server 2008 R2 or prior), or C:\Windows\Microsoft.NET\assembly\GAC_MSIL folder (for SQL Server 2012 or later).

Note that the above script is only for SSIS 2008. If you are working on a different SSIS platform, the GAC assembly would reside in a different folder depending on the version of SSIS you are using.

This step is necessary, its purpose is to make this library available for reference when writing Script component or Script task. Our installation package doesn't install the library to such folder, since writing Script Component or Task is not a common requirement considering the rich feature set that we offer through the toolkit.

NOTE that you shouldn't need to do this on your SSIS server where you deploy the developed SSIS packages, it is only required for your SSIS development system.

After you have the file copied to the relevant folder, you can start developing your SSIS script component or task.

Writing SSIS Script Component

First, you would create a SSIS Integration Services project in Visual Studio (BIDS, or SSDT).

In the automatically created SSIS package, create a new SSIS data flow task.

Add a Script Component to the data flow task, and select a type for the script component when asked. You have three options available including Source, Destination and Transformation. You need to choose a type based on what you need to achieve using the script component.

Double click the script component to open its Editor window.

Navigate to the Inputs and Outputs page, define the input/outputs that you might necessarily need and their input/output columns, the following is a simple sample of my script component which I used as a Source component.

Navigate to the Connection Managers page, make your CRM connection manager(s) available to the script component by adding the necessary ones to the list.

Navigate back to the Script page, and click "Edit Script..." button to bring up the script component's development environment.

Right click the script project in Project Explorer window, and select Properties from the context menu.

Change the script application's Target Framework to .NET Framework 3.5 (or 4.0) if it is currently .NET Framework 2.0.

Add the following three references to the script component project (Right click
References in
Project Explorer window, and select
Add Reference...)

Add the following lines to the beginning of your script component's code (You would need to change those
using statements if you are using different service endpoint which should be fairly easy to do)

using KingswaySoft.DynamicsCrmServices.Soap2011.OrganizationService;
using KingswaySoft.DynamicsCrmServices.Soap2011.OrganizationService.Messages;
using KingswaySoft.DynamicsCrmServices.Soap2011.OrganizationService.Metadata;
using KingswaySoft.DynamicsCrmServices.Soap2011.OrganizationService.Query;
using KingswaySoft.IntegrationToolkit.DynamicsCrm;

You can use the following code in the script component's
AcquireConnections method to get access to the CrmConnection object which you can later use to instantiate CRM organization service (SOAP 2011) or CrmService (SOAP 2007 or 2006).

After you have got the service handle, you can do in whichever way that you would like to interact with CRM server.

We have tried to make our toolkit as compatible as possible with CRM SDK, so technically any code that you have written for CRM SDK can be copied and pasted without making much changes except the namespace references.

Writing SSIS Script Task

Writing SSIS Script Task is very similar to the above procedures, except there aren't the following two pages

Inputs and Outputs

Connection Managers

For this reason, step 5 and 6 in above procedures are not applicable to SSIS Script Task.

And in step 12, the following is the way to get access to the CrmConnection object.

Note that the package was written in a version before our v7.1 release. If you are using v7.1 or later, you would need to make a bit minor changes to make it work. You can find such difference in the above step 12.

Have a question?

Should you have any questions about the practice discussed in this blog post, please feel free to contact us, we are more than happy to assist you.