September 11, 2008 at 3:04 pm
I've done this before in the past but can't seem to find my source code on how I did it. Basically I have a data flow task which has a OLEDB source and a recordset detination. The recordset destination is saving the results to a SSIS object variable called rsSummaryData
In the next contol flow task I have a script task. In the script task I'm trying to convert the recordset into a data table. I've tried it two ways and neither work.
#1
Try
Dim dt As DataTable = New DataTable
Dim da As SqlDataAdapter = New SqlDataAdapter
da.Fill(dt, Dts.Variables("rsSummaryData").Value)
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Error message
#2
Try
Dim dt As DataTable = New DataTable
dt = Dts.Variables("rsSummaryData").Value
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Error message: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataTable'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
Neither work. Can anyone see what I'm doing wrong?
Thanks
Strick
September 11, 2008 at 5:42 pm
June 15, 2011 at 11:47 pm
Since you use oleDB, you cannot use SQLDataAdapter
so to fix it :
#1
Try
Dim dt As DataTable = New DataTable
Dim da As OleDbDataAdapter = New OleDbDataAdapter 'use oleDBAdapter
da.Fill(dt, Dts.Variables("rsSummaryData").Value)
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Error message
Don't forget to import System.Data.OleDb.
Thanks,
JS
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply