• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

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

0
TeknikDev
Asked:
TeknikDev
  • 4
  • 2
1 Solution
 
dannygonzalez09Commented:
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
0
 
TeknikDevAuthor Commented:
Yeah thats exactly the issue. I am trying to use OLEDB, whats the proper syntax? The link only show ADO.
0
 
dannygonzalez09Commented:
Can you post your connection string?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dannygonzalez09Commented:
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
0
 
TeknikDevAuthor Commented:
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

0
 
dannygonzalez09Commented:
Syntax :
Data Source=PathToServer;Initial Catalog=Database;Integrated Security=SSPI;Application Name=PackageName;

something like this should be good, give it a try
Data Source=ABCSQLPRD;Integrated Security=SSPI;Initial Catalog=ABCPROD
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now