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 a SSIS version that supports using C# programming language for the script component/task.

Preparation

In order to be able to work with CRM/CDS connection managers in SSIS Script Component or Script Task, you need to find KingswaySoft.DynamicsCrmServices.dll file from your Windows GAC folder, and make it available to your script component or task for reference purpose.

• For v23.1 or earlier of our releases, you should find the file from the under the %windir%\assembly\GAC_MSIL\KingswaySoft.DynamicsCrmServices\1.0.0.0__705df8e0751bcea7\ folder. You will likely have to copy the file to a particular folder in order to properly reference it.
  
• For v23.2 or later, you should find the file from under the %windir%\Microsoft.NET\assembly\GAC_MSIL\KingswaySoft.DynamicsCrmServices\v4.0_1.0.2019.0__705df8e0751bcea7\ folder.

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.2 (or .NET Framework 4.6 at minimum if working with our v20.1 release).
   • When working with SSIS 2008 R2 (only available if working with v23.1 or earlier), 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)
      
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, we have prepared 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. The package was preprared for SSIS 2008 R2, and it should automatically upgrade to any higher SSIS versions that you might be using.

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