SSIS ODBC Source Component

Hot Topic

Management Studio and SQL Server 2000
SQL Server Destination Error when running multiple packages
SQL Server Thrashing CPU
Connecting reporting services
Setting up Sql Mobile subscription
Dynamic data flow
Error 1418 and Good Basic Setup Guide
Why no standard toolbar in SQL 2005?
Tell to split on change of ID
Error Connecting to Database Engine

SQL Server

Recovery of database
Port 1433
Unable to synchronize the row because the row was updated by a different process outside of replication.
Merge Join Problem
How to Convert SSIS DTS Package TO EXE Applcation
Setting up SQL 2005 Express
Page orientation for physical pages
Error that has nothing to do with my cube
How to run "Execute Package" Task Conditionally in SSIS?
job failing

SSIS ODBC Source Component

I need to create an ODBC source script component that outputs into SQL Server. When I debug I get the following error message:

Error at Data Flow Task [Script Component [1]]: System.InvalidCastException: Unable to cast object of type 'System.Data.Odbc.OdbcConnection' to type 'System.Data.SqlClient.SqlConnection'. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)Error at Data Flow Task [DTS.Pipeline]: component "Script Component" (1) failed validation and returned error code 0x80004002.

Here the problem code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent
Dim connMgr As IDTSConnectionManager90
Dim sqlConn As SqlConnection
Dim sqlReader As SqlDataReader

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.PP
sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
End Sub

Public Overrides Sub PreExecute()
Dim cmd As New SqlCommand("SELECT Solution_Code_From, Solution_Code_To FROM Solconv", sqlConn)
sqlReader = cmd.ExecuteReader
End Sub

Public Overrides Sub CreateNewOutputRows()
Do While sqlReader.Read
With SolutionOutputBuffer
.AddRow()
.solcodefr = sqlReader.GetString(1)
.solcodeto = sqlReader.GetString(0)
End With
Loop
End Sub

Public Overrides Sub PostExecute()
sqlReader.Close()
End Sub

Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(sqlConn)
End Sub

End Class

Would appreciate any advice.

Thanks in advance,

Pozzled

Answer this question

SSIS ODBC Source Component

  • Sniper167

    Jason,

     

    Thanks for the reply. I changed the code as you suggested. That removed the invalid cast problem but when I ran it I got the following error message:

    ERROR [07002] [Microsoft][ODBC Paradox Driver] Too few parameters. Expected 1.

       at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
       at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
       at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
       at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Odbc.OdbcCommand.ExecuteReader()
       at ScriptComponent_7d326c5a5b6148518a34136339cce084.ScriptMain.PreExecute()
       at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()

    Thanks,

    Puz

  • allan521

    The object being returned by the AcquireConnection() method is a System.Data.Odbc.OdbcConneciton. You are trying to use it as a System.Data.SqlClient.SqlConnection.

    Change the using System.Data.SqlClient statement to using System.Data.Odbc. Then change all the objects prefixed with Sql to a prefix of Odbc (ex. SqlCommand -> OdbcCommand)

  • Omogha

    Jason,

    Thanks. It now works. Here's the code:

    Imports System
    Imports System.Data
    Imports System.Data.Odbc
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public Class ScriptMain
    Inherits UserComponent

    Dim odbcConn As OdbcConnection
    Dim odbcCmd As OdbcCommand
    Dim odbcReader As OdbcDataReader

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
    Dim connectionString As String
    connectionString = Me.Connections.PP.ConnectionString
    odbcConn = New OdbcConnection(connectionString)
    odbcConn.Open()
    End Sub

    Public Overrides Sub PreExecute()
    odbcCmd = New OdbcCommand("SELECT solution_code_fr, solution_code_to FROM Solconv", odbcConn)
    odbcReader = odbcCmd.ExecuteReader
    End Sub

    Public Overrides Sub CreateNewOutputRows()
    Do While odbcReader.Read
    With solutionoutputBuffer
    .AddRow()
    .solfrom = odbcReader.GetString(0)
    .solto = odbcReader.GetString(1)
    End With
    Loop
    End Sub

    Public Overrides Sub PostExecute()
    odbcReader.Close()
    End Sub

    Public Overrides Sub ReleaseConnections()
    odbcConn.Close()
    End Sub

    End Class

  • halosome

    The error is due to:

    connMgr = Me.Connections.PP
    sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

    Most likely, the connection 'PP' above is not an object of type SqlConnection.

    Cast it to the type you are using (e.g. OleDbConnection).

    HTH,

    Nitesh



  • Mike D12995

    Thanks Nitesh.

    I take it your suggestion was the same as that from Jason Gerard.

    Regards,

    Puz

  • MaggieC

    This looks like an error with the SQL you are trying to execute. Trying running it from a query tool for your database to ensure it works.
  • SSIS ODBC Source Component
Answer this question