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

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.

Archive

October 2024 1 September 2024 1 August 2024 2 July 2024 1 June 2024 1 May 2024 1 April 2024 2 March 2024 2 February 2024 2 January 2024 2 December 2023 1 November 2023 1 October 2023 2 August 2023 1 July 2023 2 June 2023 1 May 2023 2 April 2023 1 March 2023 1 February 2023 1 January 2023 2 December 2022 1 November 2022 2 October 2022 2 September 2022 2 August 2022 2 July 2022 3 June 2022 2 May 2022 2 April 2022 3 March 2022 2 February 2022 1 January 2022 2 December 2021 1 October 2021 1 September 2021 2 August 2021 2 July 2021 2 June 2021 1 May 2021 1 April 2021 2 March 2021 2 February 2021 2 January 2021 2 December 2020 2 November 2020 4 October 2020 1 September 2020 3 August 2020 2 July 2020 1 June 2020 2 May 2020 1 April 2020 1 March 2020 1 February 2020 1 January 2020 1 December 2019 1 November 2019 1 October 2019 1 May 2019 1 February 2019 1 December 2018 2 November 2018 1 October 2018 4 September 2018 1 August 2018 1 July 2018 1 June 2018 3 April 2018 3 March 2018 3 February 2018 3 January 2018 2 December 2017 1 April 2017 1 March 2017 7 December 2016 1 November 2016 2 October 2016 1 September 2016 4 August 2016 1 June 2016 1 May 2016 3 April 2016 1 August 2015 1 April 2015 10 August 2014 1 July 2014 1 June 2014 2 May 2014 2 February 2014 1 January 2014 2 October 2013 1 September 2013 2 August 2013 2 June 2013 5 May 2013 2 March 2013 1 February 2013 1 January 2013 1 December 2012 2 November 2012 2 September 2012 2 July 2012 1 May 2012 3 April 2012 2 March 2012 2 January 2012 1

Tags