SSIS package data flow - SSIS Script Component Data Flow: 'VALUE' Column Results in 'Invalid Column Name' Error.

Question

SSIS package data flow - source is script component - source has a column named 'VALUE' - the error is 'Invalid column name VALUE'. I tried [VALUE], and ""VALUE"" AND "VALUE" in various places. My connection is to a sql server database and the connection is ADO.NET connection. I am using visual studios 2019.

Here is my script:

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using Microsoft.SqlServer.Dts.Pipeline; // Make sure you have this using statement

[SSISScriptComponentEntryPoint]

public class ScriptMain : UserComponent

{

public override void CreateNewOutputRows()

{

    string tableName = Variables.AttributeTableName;

    string sql = $@"

        select

ID AS ID,

TRIBUTEID,

VALUE,

ISNULL(COMMENT,' '),

ISNULL(STARTDATE, CAST('19010101' AS DATE)),

ISNULL(ENDDATE, CAST('19010101' AS DATE)),

ADDEDBYID,

CHANGEDBYID,

ISNULL(DATEADDED, CAST('19010101' AS DATE)),

ISNULL(DATECHANGED, CAST('19010101' AS DATE)),

ISNULL(TS, CONVERT(timestamp, '1901-01-01'))

        FROM dbo.[{tableName}]";

    // Get connection string from connection manager

    IDTSConnectionManager100 connMgr = this.Connections.MyConn;

    SqlConnection sqlConn = (SqlConnection)connMgr.AcquireConnection(null);

    using (SqlCommand cmd = new SqlCommand(sql, sqlConn))

    using (SqlDataReader reader = cmd.ExecuteReader())

    {

        while (reader.Read())

        {

            Output0Buffer.AddRow();

            Output0Buffer.ID = reader.GetGuid(0);

            Output0Buffer.TRIBUTEID = reader.GetGuid(1);

            Output0Buffer.VALUE =  reader.GetString(2);

            Output0Buffer.COMMENT = reader.IsDBNull(3) ? "" : reader.GetString(3);

            Output0Buffer.STARTDATE = reader.GetDateTime(4);

            Output0Buffer.ENDDATE = reader.GetDateTime(5);

            Output0Buffer.ADDEDBYID = reader.GetGuid(6);

            Output0Buffer.CHANGEDBYID = reader.GetGuid(7);

            Output0Buffer.DATEADDED = reader.GetDateTime(8);

            Output0Buffer.DATECHANGED = reader.GetDateTime(9);

// Output0Buffer.TSLONG = reader.GetInt64(10);

        }

    }

    connMgr.ReleaseConnection(sqlConn);

}

}

Answer 1

I found the problem. The table name is passed into the script, and that name is retrieved from another table. All the referenced tables are supposed to have the same structure, but one of them does not. Specifically, one table is missing the column named 'VALUE', which causes the script to fail when it tries to query it. All I needed to do initially was to put brackets around [VALUE], and after that I needed to not assume that every table referenced had a column named VALUE.