Writing Script Component or Script Task using CRM Connection Manager

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).

The following is the command line that you can use to copy the file.

REM for SSIS 2008 R2 or prior
COPY %windir%\assembly\GAC_MSIL\KingswaySoft.DynamicsCrmServices\1.0.0.0__705df8e0751bcea7\*.* %windir%\Microsoft.NET\Framework\v2.0.50727

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

1. First, you would create a SSIS Integration Services project in Visual Studio (BIDS, or SSDT).
2. In the automatically created SSIS package, create a new SSIS data flow task.
3. 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.
4. Double click the script component to open its Editor window.
5. 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.
   
6. 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.
   
7. Navigate back to the Script page, and click "Edit Script..." button to bring up the script component's development environment.
8. Right click the script project in Project Explorer window, and select Properties from the context menu.
9. Change the script application's Target Framework to the proper version based on the SSIS version that you are working with.
   • When working with SSIS 2012 or above, the Target Framework should be .NET Framework 4.6.1 (or .NET Framework 4.6 at minimum if working with our v20.1 release).
   • When working with SSIS 2008 R2, the Target Framework should be .NET Framework 3.5.
     
     
     
10. Add the following three references to the script component project (Right click References in Project Explorer window, and select Add Reference...)
    • System.Runtime.Serialization (You can find this in Add Reference window’s .NET tab)
    • KingswaySoft.IntegrationToolkit.DynamicsCrm (You can find this in Add Reference window’s .NET tab)
    • KingswaySoft.DynamicsCrmServices (You need to use Add Reference window’s Browse tab to find the file you previously copied to your file system, C:\Windows\Microsoft.NET\Framework\v2.0.50727)
      
11. 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;

12. 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).

    var connMgr = this.Connections.CrmConnectionManagerContoso;
    var connectionString = (string)connMgr.AcquireConnection(null);
    var conn = new CrmConnection(connectionString);

    Note that if you are using v7.0 or earlier, the way to instantiate the CrmConnection object is a little different, and it should be something like the following.

    var connMgr = this.Connections.CrmConnectionManagerContoso;
    var conn = (CrmConnection)connMgr.AcquireConnection(null);

    Note that you might need to have conn variable at class level, so that you can use it across the entire script class.
13. Then you can use the following code (most likely in other methods of the same class) to instantiate a CRM service handle

    var orgService = (IOrganizationService)conn.GetCrmService(); // SOAP 2011

    or

    var crmService = (CrmService)conn.GetCrmService(); // SOAP 2007 or 2006

    Note that if your connection manager uses the OAuth authentication option, you should not cache the orgService variable for repetitive use. You want to make sure that the above line of code is called every single time before you perform any service calls using the variable. In doing so, our software makes sure that the service object refreshes the token properly. Otherwise your connection will fail at the mark of one hour, which is how long an OAuth access token is valid for.

14. 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.

var connectionString = (string)Dts.Connections["Contoso"].AcquireConnection(null);
var conn = new CrmConnection(connectionString);

Note that if you are using our v7.0 release or earlier, the script should be something like the following:

var conn = (CrmConnection)Dts.Connections["Contoso"].AcquireConnection(null);

Contoso is my CRM connection manager's name, you would change it to whatever your CRM connection manager's name might be.

Sample SSIS Package

To help make it easy to understand, I have uploaded a sample SSIS package that includes both a sample script task and script component, which you can try out by downloading from the link below.

• CrmScriptCode.zip (Sample SSIS Package for SSIS 2008 R2)

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.