Accessing an ODBC Managed Connection in a Script Task

Question

Accessing an ODBC Managed Connection in a Script Task

danghita74

SSC Enthusiast

Points: 180
More actions
March 9, 2016 at 10:59 pm

#399831

Hi guys
I'm using an ODBC driver to access a proprietary file type database (having multiple files). So I have multiple managed ODBC connections defined, each using a DSN to a different file. My problem is that each time I read a file (an entity), SSIS will open it and there is no way to close it after that. The connection will remain open even if I close Visual Studio. The real problem comes when I need to open the next file to access data inside, the driver's SDK needs that all other connections to be close.
It sound more complicated then really is.. All I want is to access this Managed Connection before moving to the next file, eventually in a Script Task, and close the connection "manually". Something simple like "Dts.Connections("ODBC_File1").ReleaseConnection" where "ODBC_File1" is the name of the connection in my package
Thank you so much

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247200 More actions · Answer 1

danghita74 (3/9/2016)
Hi guys
I'm using an ODBC driver to access a proprietary file type database (having multiple files). So I have multiple managed ODBC connections defined, each using a DSN to a different file. My problem is that each time I read a file (an entity), SSIS will open it and there is no way to close it after that. The connection will remain open even if I close Visual Studio. The real problem comes when I need to open the next file to access data inside, the driver's SDK needs that all other connections to be close.
It sound more complicated then really is.. All I want is to access this Managed Connection before moving to the next file, eventually in a Script Task, and close the connection "manually". Something simple like "Dts.Connections("ODBC_File1").ReleaseConnection" where "ODBC_File1" is the name of the connection in my package
Thank you so much

You could try *something* like this (untested).

ConnectionManager cm = Dts.Connections["insert connection name here"];

System.Data.Odbc.OdbcConnection oConn = (System.Data.Odbc.OdbcConnection) cm.AcquireConnection(Dts.Transaction);

if (oConn.State == ConnectionState.Open)

{

oConn.Close();

}

danghita74 SSC Enthusiast Points: 180 More actions · Answer 2

Thank you Phil

I thought would be something like this.. unfortunately I do not have enough experience with scripting inside SSIS. And I'm a VB guy, not C# (nobody's perfect, right?)

What I also noted is every time I'm opening a connection, a Windows process starts. I might kill this process as well in a task_script with same results, closing the connection.

Any inputs for a VB slave?

Much appreciated!

Phil Parkin SSC Guru Points: 247200 More actions · Answer 3

danghita74 (3/10/2016)
Thank you Phil
I thought would be something like this.. unfortunately I do not have enough experience with scripting inside SSIS. And I'm a VB guy, not C# (nobody's perfect, right?)
What I also noted is every time I'm opening a connection, a Windows process starts. I might kill this process as well in a task_script with same results, closing the connection.
Any inputs for a VB slave?
Much appreciated!

There are lots of sites which will do the translation for you. See here, for example.