ScriptBuffer throws NullReferenceException when called from an Event as part of a loop - SSIS Script Component

Question

I have an SSIS package which calls a Data Flow Task as part of a loop which iterates different end-point addresses (out of scope).

The Data Flow Task has a source Script Component responsible for calling a REST API and creating a row for each result.

There are 3 output buffers; 1. actual data row 2. error row 3. monitoring

The monitoring buffer used for telemetry and is populated through an event (EventHander) that is fired every time the API makes a request.

During the first iteration of the ForEach int the Control Flow loop, everything runs as expected, all the buffers produce the correct rows.

However, during the next iterations, the monitoring buffer which is populated within the event throws;

System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket)

I don't understand why the MonitoringBuffer is not initialised in the proceeding iterations.

The exception occurs while calling MonitoringBuffer.AddRow();.

Here's the whole Script Component simplified for readability:

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    private string ClientCode { get { return Variables.ErplyClientCode; } }
    private string Username { get { return Variables.ErplyUsername; } }
    private string Password { get { return Variables.ErplyPassword; } }
    private bool IsTest { get { return Variables.IsTest; } }
    private int ErplyRecordsPerPage { get { return Variables.ErplyRecordsPerPage; } }
    private string ErplyDebugOutputPath { get { return Variables.ErplyDebugOutputPath; } }
    private DateTime ChangeSince { get { return Variables.ChangeSince; } }
    private int records { get; set; }
    private int errors { get; set; }
    private string rawFolder { get; set; }
    public override void PreExecute()
    {
        base.PreExecute();
    }

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

    public override void CreateNewOutputRows()
    {
        ErplyAPI.OnPreRequestEvent += new EventHandler<EAPIEvent>(ErplyAPI_OnPreRequestEvent);

        var staff = ErplyAPI.getStaff(ClientCode, Username, Password, ChangeSince, ErplyRecordsPerPage, IsTest);
        foreach (var p in staff.List)
        {
            try
            {
                if (!p.IsError)
                {
                    EmployeeBuffer.AddRow();
                    EmployeeBuffer.employeeID = p.employeeID;
                }
                else
                {
                    ErrorBuffer.AddRow();
                    ErrorBuffer.employeeID = p.employeeID;
                    ErrorBuffer.Error = p.Error.Message.Trim() + "\n" + p.Error.StackTrace;
                    errors++;
                }
                records++;
            }
            catch (Exception ex)
            {
                this.ComponentMetaData.FireWarning(0, "Script", ex.Message + "\n" + ex.StackTrace, string.Empty, 0);
            }

        }

        EmployeeBuffer.SetEndOfRowset();
        ErrorBuffer.SetEndOfRowset();

    }

    private void ErplyAPI_OnPreRequestEvent(object sender, EAPIEvent e)
    {
        var request = string.Empty;
        var sessionKey = string.Empty;
        bool fireAgain = true;

        if (e == null)
        {
            ComponentMetaData.FireWarning(0, "SC_ERPLY_API", string.Format("EAPIEvent is NULL in ErplyAPI_OnPreRequestEvent. Amonit did not log the Erply request."), string.Empty, 0);
            return;
        }

        if (e.eAPI == null)
        {
            ComponentMetaData.FireWarning(0, "SC_ERPLY_API", string.Format("EAPIEvent.eAPI is NULL in ErplyAPI_OnPreRequestEvent. Amonit did not log the Erply request."), string.Empty, 0);
            return;
        }

        try
        {
            if (e.Parameters != null && e.Parameters.ContainsKey("request"))
                request = e.Parameters["request"].ToString();

            if (request != "verifyUser" && e.Parameters != null && e.Parameters.ContainsKey("sessionKey"))
                sessionKey = e.Parameters["sessionKey"].ToString();
        }
        catch (Exception ex)
        {
            ComponentMetaData.FireWarning(0, "SC_ERPLY_API", string.Format("Error occurred assigning variables from EAPIEvent parameters in ErplyAPI_OnPreRequestEvent. {0} {1}", ex.Message, ex.StackTrace), string.Empty, 0);
        }

        try
        {
            MonitoringBuffer.AddRow(); // Exception occurs here
            MonitoringBuffer.Request = ResizeString(request, 255);
            MonitoringBuffer.SessionKey = ResizeString(sessionKey, 128);
        }
        catch (Exception ex)
        {
            var message = string.Format("Error occurred outputting Erply request in ErplyAPI_OnPreRequestEvent. {0} {1}", ex.Message, ex.StackTrace);

            MonitoringBuffer.ErrorMessage = ResizeString(message, 8000);

            ComponentMetaData.FireWarning(0, "SC_ERPLY_API", message, string.Empty, 0);
        }
        finally
        {
            MonitoringBuffer.EndOfRowset();
        }
    }
}

score 0 · Accepted Answer · answered May 08 '20 at 00:46

I sorted the problem out.

The exception was being raised when the variable dispenser was being accessed from the Event. For some reason the GetValueWithContext(ScriptComponent.EvaluatorContext) is being dropped during the second call. Why this happens is beyond me.

The solution is simple, assign the variables from the variables dispenser to a local property or variable in the OnPreExecute function.

It's also good practice to not call the variable dispenser in the CreateNewOutputRows as it cause variable locking.

score 0 · Answer 2 · answered Jan 16 '21 at 01:54

I ran into this issue too, but my solution was a little different -- moving the variable assignments into PreExecute() didn't help.

Instead, what I'd done is that I wanted to parse three different files, and read each of them with a Script Component. Their columns were kinda similar, so I created one Data Flow task, made sure it worked, then copied it and modified each copy to reflect the differences in the files. Running each individual Data Flow task was successful, but when I tried to run two of them, one after the other in a loop, I got a NullReferenceException from HostPrimeOutput() after calling the OutputBuffer.AddRow() method in my Script Component.

It turns out that when I copied each Data Flow task, the Script Components all kept the same namespace, and I guess it doesn't like that. So, I created brand new Script Components, set up all the output columns again (ugh!), copied the body of the script over, and it's happy.

ScriptBuffer throws NullReferenceException when called from an Event as part of a loop - SSIS Script Component

2 Answers2