1

In my SSIS package (SQL Server 2012) I set a stored procedure result set in an object variable using the Execute SQL Task. The connection manager I use has the following connection string:

Data Source=localhost;Initial Catalog=<myCatalog>;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;

My store procedure output is a result set containing a DateTimeOffset column and a Time(3) column. The Execute SQL Task executes succesfully. Then I have a Script Task in which I use the object variable set by the Execute SQL Task in this way:

OleDbDataAdapter da = new OleDbDataAdapter();
DataTable dt = new DataTable();

// Extract the data from the object variable into the table
da.Fill(dt, Dts.Variables["User::ResultsetObj"].Value);

When I execute the package I get this error:

ERROR: OleDbDataAdapter internal error: invalid row set accessor: Ordinal=20 Status=UNSUPPORTEDCONVERSION.

By default the Script Task uses the .NET Framework 4. What's wrong with the OleDbAdapter? Are these data types really unsupported?

Thank you.

lucazav
  • 858
  • 9
  • 24

2 Answers2

1

For a quick answer: https://msdn.microsoft.com/en-us/library/cc668759%28v=vs.110%29.aspx --> realy not supported.

I did a smal Test (in VS) with the SqlClient:

someDataTable = New DataTable()
someDataTable.Load(connection.NewCommand("SELECT SYSDATETIMEOFFSET()").ExecuteReader)
?someDataTable.Rows(0).Item(0)
{03.07.2015 17:24:27 +02:00}
    System.DateTimeOffset: {03.07.2015 17:24:27 +02:00}

Maybe you could try not to convert data with the OleDbDataAdapter but with the DataTable by using an IDataReader.

Beinfreiheit
  • 13
  • 2
  • Thank you @Beinfreiheit. I already have an Object variable to loop. The only way I know to do that is this one: http://stackoverflow.com/a/18218998/416988. Do you know another one? – lucazav Jul 03 '15 at 16:20
0

I deleted the Execute SQL Task and the OleDb Connection Manager. Then I used the SQL Client inside the Script Task in this way:

string connStr = "Data Source=localhost;Initial Catalog=myDatabase;Integrated Security=SSPI;";
DataTable dt = new DataTable();
using (SqlConnection sqlConnection = new SqlConnection(connStr))
{
    sqlConnection.Open();
    SqlCommand cmdExtendedProperies = new SqlCommand("EXEC myProcedure", sqlConnection);
    SqlDataReader reader = cmdExtendedProperies.ExecuteReader();
    dt.Load(reader);
}

All works fine.

lucazav
  • 858
  • 9
  • 24