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.

• Brad: Table it's attempting to insert into is a heap and has no rows.
• Brad: 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...
• Scott Hodgin - Retired: While not a true answer - someone else had the same problem here and decided to use SSDT for VS2012 - -stackoverflow.com/questions/36458772/…

Answer 1

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);
 }