ConnectionManagerOleDb usage in a Script Component

Hi,

I have a need to use a shared connection manager of type OLEDB inside a Script Component. I guess that the right way to get the OLEDB connection is to override the AcquireConnections method, case the shared connection manager to the ConnectionManagerOleDb interface and call the AcquireConnection method.

The problem is in the return value of the AcquireConnection method (it’s Object). My assumption is that in case of OLEDB connection usage, the AcquireConnection method returns some OLE DB COM interface. Does some of you tried to cast it to some interface/class exposed by DTS (or some standard COM interface from MDAC)?

PS I’m aware about the possibility to use ADO.NET connection managers, however it’s not a case for me because of DataReader Source limitations (OLE DB Source suits my needs much better).

I would really appreciate your answer. Thanks in advance!

Regards,

Dmytro

Hi,

I am not using override acquireconnection method for sharing connections. Instead of that I have created a global connection and created a property for opening the connection. Inside the property, if it is null I will open a new connection, otherwise I will return the existing connection. I have overriden the 'releaseconnection' method, to release that connection. It is working fine. Hope this would help u.

|||

Hi,

I am facing similar situation. Have you got a solution. It will be great if you can share the way you resolved it.

Thanks

|||

Hi,

Thanks for your reply! What is the type of the global connection that you created and what would happen if your package is executed in a transaction? Do you take transactions into consideration?

Thanks!

/Dmytro

|||

Hi,

Unfortunately I haven't found a way how to solve this issue yet. If I find it, I would definitely post a solution here.

/Dmytro

|||

Dmytro Kryvko wrote:

I have a need to use a shared connection manager of type OLEDB inside a Script Component.
...
My assumption is that in case of OLEDB connection usage, the AcquireConnection method returns some OLE DB COM interface.

This is correct, the object is the OLEDB COM object, namely OLEDB session created by IDBCreateSession::CreateSession call. For using it, see OLEDB reference in MSDN.

Note that these interfaces are not meant to be used from .NET. If you really need to use it, you should create a custom component in C++, rather than using script component.