Link to home
Start Free TrialLog in
Avatar of TeknikDev
TeknikDevFlag for United States of America

asked on

SSIS 2008 Connection Error to OLEDB

Hi, I'm getting the error and does anyone know how I can fix this?

The code is in VB.net and its Visual Studio 2008.

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

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


<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    Private connMgr As IDTSConnectionManager100
    Private Conn As SqlClient.SqlConnection
    Private Cmd As SqlClient.SqlCommand
    Private sqlReader As SqlClient.SqlDataReader


    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

        'This is the connection to your connection manager.
        connMgr = Me.Connections.Connection
        Conn = CType(connMgr.AcquireConnection(Nothing), SqlClient.SqlConnection)


    End Sub

Open in new window

Avatar of dannygonzalez09
dannygonzalez09

Looks like the error refers to this below line of code

 Conn = CType(connMgr.AcquireConnection(Nothing), SqlClient.SqlConnection)

have a look at the connection string example

http://technet.microsoft.com/en-us/library/ms136018.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1
Avatar of TeknikDev

ASKER

Yeah thats exactly the issue. I am trying to use OLEDB, whats the proper syntax? The link only show ADO.
Can you post your connection string?
you might want to remove the provider and auto translate properties from the string, if you have and use the same syntax as in the link..give that a try
Can you provide syntax? Not sure how I'm to do that.

Connection string:
Provider=SQLNCLI10.1;Data Source=ABCSQLPRD;Integrated Security=SSPI;Initial Catalog=ABCPROD

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of dannygonzalez09
dannygonzalez09

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial