Error in Script Task SSIS

Question

Post reply

Error in Script Task SSIS

WangcChiKaBastar

SSCrazy

Points: 2297
More actions
March 23, 2010 at 9:08 am

#138020

Script Task Code :

Dim conSQLConnection As SqlClient.SqlConnection

Dim i As Integer

conSQLConnection = DirectCast(Dts.Connections("connection").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)

Error : Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. 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.

Please advise ASAP

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

Elliott Whitlow

SSC Guru

Points: 102296

March 23, 2010 at 10:17 am

#1138366

I'm betting that the connection is an OLEDB and not a SQL connection. I think you probably need to use the OLEDB objects and not the SQL objects.

CEWII

WangcChiKaBastar SSCrazy Points: 2297 More actions · Answer 2

Please look at this code...

Dim connMySQL As OdbcConnection

Dim sqlCmd As OdbcCommand

Dim datareader As Odbc.OdbcDataReader

Dim tblName As String

tblName = CType(ReadOnlyVariables("table_name"), String)

connMySQL = CType(Me.Connections.MySQL.AcquireConnection(Nothing), OdbcConnection)

sqlCmd = New OdbcCommand("Select....query")

datareader = sqlCmd.ExecuteReader()

While datareader.Read()

Output0Buffer.AddRow()

Output0Buffer.Column1= datareader.Item(0).ToString()

Output0Buffer.Column2= datareader.Item(1).ToString()

Output0Buffer.Column3= Int32.Parse(datareader.Item(2).ToString())

End While

Error : Unable to cast COM object of type 'System.__ComObject' to class type 'System.String'. 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.

Connection Manager is ODBC DSN to MySQL Database.

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

First of all what statement is generating the error?

Second, I *think* that ODBC connections are wrapped in an .Net wrapper for SSIS. So I might have led you a little astray the first time..

I know this isn't exactly right but I think it might give you a basis to work from:

http://blog.stevienova.com/2009/10/07/sql-2005-ssis-pushing-data-to-mysql-using-script-component-destination/

CEWII

WangcChiKaBastar SSCrazy Points: 2297 More actions · Answer 4

I think I found the line the error could be originating out of...

Error is at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables90.get_Item(Object Index)

at ScriptComponent_13ebdf6566b5461a99d978dea2296191.ScriptMain.CreateNewOutputRows() in dts://Scripts/ScriptComponent_13ebdf6566b5461a99d978dea2296191/ScriptMain:line 26

at ScriptComponent_13ebdf6566b5461a99d978dea2296191.UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers) in dts://Scripts/ScriptComponent_13ebdf6566b5461a99d978dea2296191/ComponentWrapper:line 40

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)

Code :

While datareader.Read()

Output0Buffer.AddRow()

Output0Buffer.NPANXX = datareader.Item(0).ToString()

Output0Buffer.TGID = datareader.Item(1).ToString()

Output0Buffer.Rank = Int32.Parse(datareader.Item(2).ToString())

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 5

I take it you mean this line:

Int32.Parse(datareader.Item(2).ToString())

Why don't you add a msgbox and just see what you get from datareader.Item(2) before you try to manipulate it, then add the ToString. In other words build up the command until it fails. I'm wondering if perhaps the Parse is your problem but I'm not sure.

CEWII