SSIS Script Component as Source AddRow Null Exception

Question

I have a SSIS package with single Dataflow Task. The first step in this Dataflow is a Script component setup as a source, the default 'Output 0' has been renamed to 'Out' and it has a single column 'UserID' of W_STR, length 11. It is being routed to an OLEDB Destination table with a single column called [User ID] of type NVARCHAR(11). The mappings are set.

When calling OutBuffer.AddRow() I get a Null Reference Exception. I've deleted the script component and re-created it several times, removing all other logic to the point that the script component looks like this:

public override void PreExecute()
{
    base.PreExecute();
    /*
     * Add your code here
     */
}

/// <summary>
/// This method is called after all the rows have passed through this component.
///
/// You can delete this method if you don't need to do anything here.
/// </summary>
public override void PostExecute()
{
    base.PostExecute();
    /*
     * Add your code here
     */
}

public override void CreateNewOutputRows()
{
    /*
      Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
      For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
    */
    OutBuffer.AddRow();
    OutBuffer.UserID = "1234567";
}

Yet still it fails on AddRow()... I'm using SQL Server Data Tools 2015 with my project in package deployment mode and the target SQL version set to SQL 2012.

  at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.AddRow()
   at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.AddRow()
   at OutBuffer.AddRow() in c:\Users\oka2bbf\AppData\Local\Temp\Vsta\a20c6c234a834b8e924d02b641c4b06d\BufferWrapper.cs:line 45
   at ScriptMain.CreateNewOutputRows() in c:\Users\user\AppData\Local\Temp\Vsta\a20c6c234a834b8e924d02b641c4b06d\main.cs:line 111
   at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers, OutputNameMap OutputMap) in c:\Users\user\AppData\Local\Temp\Vsta\a20c6c234a834b8e924d02b641c4b06d\ComponentWrapper.cs:line 49
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)

Edit: If I change the project to SQL 2014 or SQL 2016 it works. It seems to be an issue with SQL 2012, however I'm deploying to SQL 2012, so I need to have it set for SQL 2012. I have Development Edition of SQL 2012 installed (the only SQL instance I have installed locally) and as I'm using SQL Server Data Tools 2015 to edit the package.

Table it's attempting to insert into is a heap and has no rows. — Brad
– Brad, Commented May 6, 2017 at 20:44
I remove the OLEDB Destination, added a Row Count destination and created a variable to use with the Row Count. I still receive the Null Exception error when AddRow is called... — Brad
– Brad, Commented May 6, 2017 at 20:56
While not a true answer - someone else had the same problem here and decided to use SSDT for VS2012 - -stackoverflow.com/questions/36458772/… — Scott Hodgin - Retired
– Scott Hodgin - Retired, Commented May 8, 2017 at 11:38

questionto42 · Accepted Answer · 2024-04-06 19:07:24Z

C# cannot just output a null:

It therefore has the data type DBNull.Value that is understood both by C# and in SQL.
Or you set the column property for is_null to true.
Or you set the mycolumn1_IsNull to true, C# has such a suffix "_IsNull" for each variable only to save whether it is null or not.

And to find whether a value is a null, you need to take the Row object during the loop and then put together the column name inside the Row with the suffix _IsNull at the end of the Row's column name.

Mind that the column name is the shortened original column name since space, minus, and underscore are dropped, example: my_column-1 b becomes mycolumn1b. Thus, my_column-1 b will get a twin column called mycolumn1b_IsNull.

Here is a code that loops over each row (the Row object), checks for null, and outputs an understandable null if needed. It is a smaller snippet from the code at How do I create and fill a temporary table from a data source by means of a C# DataTable without ever leaving the SSIS Data Flow Task? - DBA SE.

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Create DataRow to store values
        DataRow dr = dt.NewRow();

        // Iterate over input columns to get original and then modified column names for the chosen Row
        foreach (IDTSInputColumn100 inputColumn in this.ComponentMetaData.InputCollection[0].InputColumnCollection)
        {
            // Get the original column name
            string originalColumnName = inputColumn.Name;

            // Get modified column name for checking null values in Row object
            string modifiedColumnName = originalColumnName.Replace("_", "").Replace(" ", "").Replace("-", "");

            // Check if the column is null
            PropertyInfo isNullProperty = typeof(Input0Buffer).GetProperty(modifiedColumnName + "_IsNull");

            if (isNullProperty != null)
            {
                // Handle null value as needed
                // For example, you can set it to DBNull.Value
                dr[originalColumnName] = DBNull.Value;
                continue; // Skip to the next column
            }

            // Get property with matching name from Row object
            PropertyInfo property = typeof(Input0Buffer).GetProperty(modifiedColumnName);

            object value = property.GetValue(Row);

            // Add value to DataRow
            dr[originalColumnName] = value;
        }
        // Add populated DataRow to DataTable
        dt.Rows.Add(dr);
    }

Stack Exchange Network

SSIS Script Component as Source AddRow Null Exception

1 Answer 1

Linked

Hot Network Questions

SSIS Script Component as Source AddRow Null Exception

1 Answer 1

Linked

Related

Hot Network Questions