Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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