CODEWITHSUNDEEP
vb.netssis
user1668795
user1668795

Reputation: 57

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

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?

Upvotes: 0

Views: 1465

Answers (2)

JohnN
JohnN

Reputation: 23

I've hit the same issue. The error message I was getting was: "Command parameter[0] '' data value could not be converted for reasons other than sign mismatch or data overflow."

In the MSDN article referenced above I cannot see any reference to OLE DB - TVPs seem to be SqlClient only.

Also, in the System.Data.OleDB.OleDbType enumeration there is no option for Structured.

I changed my OLEDB connection to ADO.Net Connection and it worked fine.

Upvotes: 0

billinkc
billinkc

Reputation: 61221

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.

Upvotes: 0

Related Questions