2

I have a script transformation component that takes input from an OLEDB source and, for each row, executes a stored procedure with parameters based on the row value. I have used this functionality in past solutions without issue however, it's now throwing the dreaded "Object reference not set to an instance of an object". I've compared all of the settings (e.g. Framework version, input/output, assembly references, etc) that I can think to check between the two solutions to no avail. There only seems to be an issue with actually setting the variables, i.e. I can instantiate a public counter, output a message with the counter (in the ProcessInputRow method) and increase the counter -- this will fire as many messages as there are rows in the source table. I've also tried .ToString() for the variable values without any luck.

Runtime Error enter image description here

Data Flow Task

enter image description here

Input Columns enter image description here

Connection Managers enter image description here Script

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;


[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    SqlConnection conn = new SqlConnection();
    IDTSConnectionManager100 connMgr;

    public override void AcquireConnections(object Transaction)
    {
        connMgr = base.Connections.adoAdminsys;
        conn = (SqlConnection)connMgr.AcquireConnection(null);
    }

    public override void PreExecute()
    {
        //base.PreExecute();
    }

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

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        string certNbr = Row.certNbr;
        int seqNbr = Row.seqNbr;

        try
        {
            SqlCommand cmd = new SqlCommand("dbo.jbb_test_insert", conn);

            cmd.Parameters.Add(new SqlParameter("@certNbr", certNbr));
            cmd.Parameters.Add(new SqlParameter("@seqNbr", seqNbr));

            cmd.ExecuteNonQuery();

        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
    }
}

Again, if I do something as display a message inside the row buffer, it will fire the exact number of time that there are rows in the source. I also enabled the Data Viewer, between the Source and the Script Component, to ensure that rows are actually being passed. Also, if I just try to display the variables (certNbr, seqNbr) in a message, I will receive the error -- so I'm convinced the issue is with the Input Rows.

jbeckom
  • 105
  • 1
  • 2
  • 10
  • 1
    Is Row.certNbr or Row.seqNbr null in your data set? Seems the most likely cause – billinkc Apr 30 '18 at 17:01
  • @billinkc -- no, all rows have a value for each attribute – jbeckom Apr 30 '18 at 17:09
  • Is `AcquireConnections()` always called implicitly in Script Transformations? Because I don't see anywhere in the code where you call it explicitly. – Tab Alleman Apr 30 '18 at 17:34
  • I can remove that code from the script and receive the same results/error... `[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { public override void PreExecute() { //base.PreExecute(); } public override void PostExecute() { //base.PostExecute(); } public override void Input0_ProcessInputRow(Input0Buffer Row) { string certNbr = Row.certNbr; int seqNbr = Row.seqNbr; MessageBox.Show(certNbr.ToString()+", "+seqNbr.ToString()); } }` – jbeckom Apr 30 '18 at 17:50
  • Ok, then if you shift the variable declarations into the `try` block, does the exception get raised? – billinkc Apr 30 '18 at 18:19
  • yes, I just get the exception 158 times (the number of rows in the source) – jbeckom Apr 30 '18 at 18:34
  • I wonder this might help when we open script to edit first time. It has some default comment laid out in class file. You are required to declare any variables prior using them in your script. #region Help: /* To use a variable in this script, first ensure that the variable has been added to either the list contained in the ReadOnlyVariables property or the list ontained in the ReadWriteVariables property of this script component. #endregion – Binoy Oct 03 '18 at 22:24

0 Answers0