0

I have created an SSIS package to transfer a large bulk amount of data. Everything works fine on all my other Execute SQL Tasks, however on my only Script Task I keep getting the error Invalid column name 'cortisol' at system.Data.OleDb.OleDbCommand.ExecuteReaderinternal(CommandBehavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() The SQL string shows the correct information and values, but when it reaches the ExecuteNonQuery it throws the error. I am using VB.Net, my code is:

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

Can anyone please help. I am using OleDb as no matter what I have tried I cannot establish a connection with the Connection manager using ADO.Net. I am running the SSIS program locally and not using Azure.

I have tried.

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()

However, no parameters are being detected when it gets to ExecuteNonQuery it says column does not except NULL values.

My table has two columns Category_Code and Category_Name.

Improve this question
5
  • Please edit your question and provide the SQL table definition (columns, data types, etc) for the table you're attempting to insert into. Commented Sep 19 at 14:44
  • 2
    Your insert statement is concatenating values into a SQL string -- that's open to SQL Injection. In your second attempt, you attempt to use parameters, but your insert statement is not using parameters -- it still has those concatenated values. Commented Sep 19 at 14:45
  • 2
    First, there's an Execute SQL Task and an OLEDB Command Transformation, there's no reason to use this code. Second, this is vulnerable to SQL injection. Right now the raw contents of the text fields are included in the SQL string, resulting in an invalid command. Don't try to quote them. Use the correct task instead. Commented Sep 19 at 14:45
  • 1
    Should you ever need to use ADO.NET, which right now you don't use parameterized queries. The queries never become part of the SQL string itself, even when the query gets executed on the server. There's no SQL injection, no mangled money or date values Commented Sep 19 at 14:47
  • 2
    transfer a large bulk amount of data. that's the job of the Dataflow task, which is always faster than hand-written code. Unlike the code in this question, a dataflow works on a stream of rows that get inserted using whatever bulk protocol is available in the database driver. It doesn't insert rows 1-by-1, it inserts them in big batches out of the box Commented Sep 19 at 14:50
Related questions
1
How to access vb.net button click event on modal popup button click event?
0
System.Data.OleDb.OleDbException invalid argument
1
oledb exception was unhandled
Load 2 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.