OLE-Db connection in Transformation Script Component

Hello,

Using the following documentation as a guide:

http://msdn2.microsoft.com/zh-cn/library/aa337080.aspx

I instantiated a new script component into an existing Data Flow in my SSIS project.

In the Script Transformation Editor, under the Connection Managers section, I associated the name dbConnManager to an already existing Connection Manager in the project.

My Connection Manager is of the type oOLEDB.

I then opened up the script designer and added the following lines of code where it said "Add your code here"

Dim myConnManager As IDTSConnectionManager90 = _

Me.Connections.ECFconnection

Dim dbConn As OleDb.OleDbConnection = _

CType(myConnManager.AcquireConnection(Nothing), OleDb.OleDbConnection)

When I test run the project I get the following error and the new script component is red:

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

I know the database connection works since I am using it in a component that executes before this new script component.

I am stuck...Any suggestions?

Unfortunately you cannot use a Connection Manager that returns a native type in managed code. This includes OLE DB and Excel.

This limitation is noted in BOL in
http://msdn2.microsoft.com/en-us/library/ms136018.aspx
and
http://msdn2.microsoft.com/en-us/library/aa337080.aspx

with this suggestion:

If you need to call the AcquireConnection method of a connection manager that returns an unmanaged object, use an ADO.NET connection manager. When you configure the ADO.NET connection manager to use an OLE DB provider, it connects by using the .NET Framework Data Provider for OLE DB. In this case, the AcquireConnection method returns a System.Data.OleDb.OleDbConnection instead of an unmanaged object. To configure an ADO.NET connection manager for use with an Excel data source, select the Microsoft OLE DB Provider for Jet, specify an Excel workbook, and then enter Excel 8.0 (for Excel 97 and later) as the value of Extended Properties on the All page of the Connection Manager dialog box.

-Doug

|||

Thank you. That was the nudge in the right direction that I needed.

Greg.