Trying to Retrieve CRM Data via an SSIS Script Component using CRM Connection Manager

(0) Share

Report

Posted on by Community Member

I have a transformation script component within a data flow task - using KingswaySoft.

However, I get the error "Object reference not set to an instance of an object" when the script task executes.

I pass an SSIS variable into the component called "ContactGUID". The component also has 1 input, "CRMRecordId", which is the GUID of the custom entity I want to retrieve the x_contact value from. The component has a CRMConnection manager and one output column added.

My first question is there anyway to debug Script Components? I have tried setting a message box/break points - but the object error appears as soon as the script component is reached within the data flow.

Here is my code:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using KingswaySoft.IntegrationToolkit.DynamicsCrm;
using KingswaySoft.DynamicsCrmServices.Soap2011;
using KingswaySoft.DynamicsCrmServices.Soap2011.OrganizationService.Query;
using KingswaySoft.DynamicsCrmServices.Soap2011.OrganizationService;
using KingswaySoft.DynamicsCrmServices.Soap2011.OrganizationService.Messages;
using KingswaySoft.DynamicsCrmServices.Soap2011.OrganizationService.Metadata;
         
using System.ServiceModel.Description;
using Microsoft.SqlServer.Dts.Pipeline;
using System.Runtime.Serialization;
#endregion

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
  
    public IOrganizationService _service;
    KingswaySoft.DynamicsCrmServices.Soap2011.OrganizationService.IOrganizationService orgService;
    public override void PreExecute()
    {
        Console.WriteLine("starting pre-execute");
        base.PreExecute();
   
        var connMgr = this.Connections.CrmConnection;
        var connectionString = (string)connMgr.AcquireConnection(null);
        var conn = new CrmConnection(connectionString);
        var orgService = (IOrganizationService)conn.GetCrmService(); // SOAP 2011
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        ColumnSet cols = new ColumnSet(
                        new String[] { "x_contact" });

        Entity contactIdentifier = orgService.Retrieve("x_contactidentifier", Row.CrmRecordId, cols);

        if (contactIdentifier.Contains("x_contact"))
       {
              Variables.ContactGUID = Convert.ToString(contactIdentifier.Attributes["x_contact"]);
       }

    }
}

When I comment out the call to "orgService" the error is no longer - which leads me to believe it may be something todo with the "Pre-Execute" Method.

Any help would be much appreciated - Thanks

Additional Information:

CRM 365 Online

SQL Server 2017

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Community Member on at

Like (0)

Report

Error when script component executes:

Was this reply helpful? Yes No
Verified answer

Daniel Cai 935 on at

Like (2)

Report
The following line of code is essentially hiding the class variable.

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

You need to make sure that you are not declaring a new local variable, so it should be something like the following.

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

Hope this is helpful.

Was this reply helpful? Yes No
Suggested answer

Michel Gueli 982 on at

Like (0)

Report

replace:

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

With:

_service = (IOrganizationService)conn.GetCrmService(); // SOAP 2011

And Replace:

Entity contactIdentifier = orgService.Retrieve("x_contactidentifier", Row.CrmRecordId, cols);

with:

Entity contactIdentifier = _service.Retrieve("x_contactidentifier", Row.CrmRecordId, cols);

The solution of Daniel Cai will also work.

Was this reply helpful? Yes No
Praveen Mulukutla 55 on at

Like (0)

Report

Hi Daniel,

Will the above code work for D365 CDS connection also? I wounder If I have to deploy my SSIS package in Azure as a pipeline and will it be able to get the required dll's?

I have similar requirement in my project.

Thank you in advance!

Was this reply helpful? Yes No
Daniel Cai 935 on at

Like (0)

Report

Hi Praveen, sorry for the late response, I think I missed the notification for this discussion thread. Yes, the code will work for CDS connection as well. If you run in Azure SSIS-IR, it should work the same. Note that what's behind an Azure SSIS-IR is a virtual machine (or container) which behaves like a typical system, and you have full control of the system during the time of provisioning including GAC'ing or copying any files if needed.

Was this reply helpful? Yes No