Solved

SSIS 2008 Connection Error to OLEDB

Posted on 2014-04-16
6
360 Views
Last Modified: 2016-02-10
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
Comment
Question by:TeknikDev
  • 4
  • 2
6 Comments
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 40005088
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
 

Author Comment

by:TeknikDev
ID: 40005096
Yeah thats exactly the issue. I am trying to use OLEDB, whats the proper syntax? The link only show ADO.
0
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 40005112
Can you post your connection string?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 40005119
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
 

Author Comment

by:TeknikDev
ID: 40006475
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
 
LVL 5

Accepted Solution

by:
dannygonzalez09 earned 500 total points
ID: 40007109
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question