VS2022中SSIS脚本任务用OleDb连接调用ExecuteNonQuery报错

我创建了一个SSIS包用于传输大量数据，其他Execute SQL任务均运行正常，但唯一的Script Task始终报错：
Invalid column name 'cortisol' at system.Data.OleDb.OleDbCommand.ExecuteReaderinternal(CommandBehavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

SQL字符串显示的信息和值看似正确，但执行ExecuteNonQuery时抛出错误。我使用VB.Net编写代码，本地运行SSIS程序（未使用Azure），因尝试多种方法无法通过ADO.Net与连接管理器建立连接，故采用OleDb。

原代码如下：

Public Sub Main()
 Try
    Dim rawConnection As Object = Dts.Connections("My_OleDb_Connection").AcquireConnection(Dts.Transaction)
    Dim myOleDbConnection As OleDbConnection = CType(rawConnection, OleDbConnection)

    Dim dtSDG As New DataTable
    Dim drSDG As DataRow

    Dim strSql As String
    strSql = "SELECT RTRIM(Group_Id) AS Group_Id, RTRIM(Parent_Group_Id) As Parent_Group_Id, RTRIM(Group_Name) AS Group_Name FROM WHO_SDG_3;"
    Using dad As New OleDbDataAdapter(strSql, myOleDbConnection)
        dad.Fill(dtSDG)
    End Using

    For Each drSDG In dtSDG.Rows
        Me.AddLocalCategory(myOleDbConnection, strDotDelimitedCategoryCode, strGroupName)
    Next

    Dts.TaskResult = ScriptResults.Success
 Catch ex As Exception
    MsgBox(ex.Message & vbCrLf & ex.StackTrace)
    Dts.TaskResult = ScriptResults.Failure
 End Try
End Sub

Sub AddLocalCategory(
        ByVal myOleDbConnection As OleDbConnection,
        ByVal Category_Code As String,
        ByVal Category_Name As String
        )
    Dim strSQL As String
    strSQL = "INSERT INTO Staging_SDG_Categories ( Category_Code, Category_Name ) " &
            " VALUES ( " & Category_Code & ", " & Category_Name & ") ; "

    Dim cmd As New OleDbCommand(strSQL, myOleDbConnection)
    Dim intRowsAffected As Integer
    intRowsAffected = cmd.ExecuteNonQuery()
End Sub

我尝试了参数化写法，但执行ExecuteNonQuery时仍提示参数未被识别，且列不允许为NULL值：

Dim strSQL As String
strSQL = "DECLARE @Category_Code VARCHAR (25), " &
"Category_Name VARCHAR (250) " &
"INSERT INTO Staging_SDG_Categories ( Category_Code, Category_Name ) " &
        " VALUES (@Category_Code, @Category_Name) ; "

Dim cmd As New OleDbCommand(strSQL, myOleDbConnection)
cmd.Parameters.Add("@Category_Code", OleDbType.VarChar, 25).Value = Category_Code
cmd.Parameters.Add("@Category_Name", OleDbType.VarChar, 250).Value = Category_Name

Dim intRowsAffected As Integer
intRowsAffected = cmd.ExecuteNonQuery()

我的表包含Category_Code和Category_Name两列，恳请提供解决方法。

问题分析与解决方案 #

错误原因 #

1. 非参数化写法的问题：直接拼接字符串会破坏SQL语法（比如值包含单引号、空格时），甚至将字段值误判为列名（报错中的cortisol就是某个Category_Name的值被当成列处理了），同时存在SQL注入风险。
2. 参数化写法的错误：OleDb不支持命名参数，需用?作为占位符；且参数顺序必须与SQL中占位符顺序一致，多余的DECLARE语句会干扰参数绑定。
3. 数据取值缺失：原代码中未从DataRow中获取实际字段值就调用AddLocalCategory，导致参数为空，触发列不允许为NULL的错误。

修正后的代码 #

1. 修正Main方法中的数据取值

For Each drSDG In dtSDG.Rows
    ' 从DataRow中读取实际字段值，替换为你需要的列名
    Dim categoryCode As String = drSDG("Group_Id").ToString().Trim()
    Dim categoryName As String = drSDG("Group_Name").ToString().Trim()
    Me.AddLocalCategory(myOleDbConnection, categoryCode, categoryName)
Next

2. 修正AddLocalCategory的参数化逻辑

Sub AddLocalCategory(
        ByVal myOleDbConnection As OleDbConnection,
        ByVal Category_Code As String,
        ByVal Category_Name As String
        )
    ' 使用?作为OleDb参数占位符
    Dim strSQL As String = "INSERT INTO Staging_SDG_Categories (Category_Code, Category_Name) VALUES (?, ?);"

    ' 使用Using确保资源自动释放
    Using cmd As New OleDbCommand(strSQL, myOleDbConnection)
        ' 按顺序添加参数，OleDb不识别参数名，只认顺序
        cmd.Parameters.Add(New OleDbParameter("", OleDbType.VarChar, 25)).Value = If(String.IsNullOrEmpty(Category_Code), DBNull.Value, Category_Code)
        cmd.Parameters.Add(New OleDbParameter("", OleDbType.VarChar, 250)).Value = If(String.IsNullOrEmpty(Category_Name), DBNull.Value, Category_Name)

        Dim intRowsAffected As Integer = cmd.ExecuteNonQuery()
    End Using
End Sub

注意事项 #

• 处理空值：若字段可能为空，需将.NET的Null转换为DBNull.Value，避免数据库报错。
• OleDb参数特性：必须用?占位，参数添加顺序要与SQL中占位符顺序完全匹配。
• 资源释放：用Using包裹OleDbCommand、OleDbConnection等资源，避免内存泄漏。

内容的提问来源于stack exchange，提问作者Sheztc1977