Solved

SSIS 2008 Connection Error to OLEDB

Posted on 2014-04-16
6
365 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

724 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