Unspecified Error in OleDbCommand.ExecuteNonQuery() function within SSIS script task

Question

I have an SSIS package, which has a FOREACH loop container and inside the loop I have a script task. The code in the script task is as given below

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Configuration

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Public Sub Main()
Try
            Dim oleConn As New OleDbConnection()
            Dim strConn As String = Dts.Variables("ConfiguredValue").Value.ToString()
            oleConn.ConnectionString = strConn
            Dim oleDA As New OleDbDataAdapter()
            Dim dt As New DataTable()
            oleDA.Fill(dt, Dts.Variables("Source_Data").Value)
            oleConn.Open()
            Dim oleComm As New OleDbCommand("Insert_Into_Destination")
            oleComm.CommandType = CommandType.StoredProcedure
            oleComm.Connection = oleConn
            oleComm.Parameters.AddWithValue("@tvp", dt)
            oleDA.InsertCommand = oleComm
            oleComm.ExecuteNonQuery()
            oleConn.Close()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
End Class

Value of the variable Dts.Variables("ConfiguredValue").Value changes in each iteration of the For Each container. And it looks like -

"Data Source=server_name;Initial Catalog=db_name;User ID=user_id;Password = Password;Provider=SQLOLEDB.1; Persist Security Info=True;Integrated Security=SSPI;Auto Translate=False;"

The problem is - when the package is executed there is an exception thrown at the ExecuteNonQuery() which shows 'Unspecified Error'.

There is no error when I try the similar code to insert an integer value. Issue is caused when it is a datatable I pass as the input parameter to the SP.

Any solution to avoid this?

You cannot pass a DataTable to a stored procedure .You need to eitehr use Table Value Parameters(TVP) or xml .Check out this MSDN link http://msdn.microsoft.com/en-us/library/bb675163.aspx — praveen

billinkc billinkc · Accepted Answer · 2013-01-16T14:32:43

I believe what you are missing is to tell the parameter what TVP it should be and it's type (last 2 lines). My table valued parameter is in C# but the same concepts will apply.

    command = new System.Data.SqlClient.SqlCommand("TwitterAdd");
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Connection = connection;

    // Assigning a table valued parameter looks much like any other parameter
    System.Data.SqlClient.SqlParameter tvp = command.Parameters.AddWithValue("@tvp", dataTable);

    // this is the only special sauce
    tvp.SqlDbType = System.Data.SqlDbType.Structured;
    tvp.TypeName = "dbo.MY_TABLE_VALUED_PARAMETER";

This in VB should be the same minus the semi-colon.

Unspecified Error in OleDbCommand.ExecuteNonQuery() function within SSIS script task

2 Answers