Search code examples
ssisssis-2012

Reusing connection manager from SSIS script task

In my SSIS package, I created ADO connection manager, so I have MyAdoManager.conmgr that has underlying connection named MyServerName.MyDbName.

Now, I'm trying to use it in the Script Task the way it's described in some manuals.

ConnectionManager cm;
System.Data.SqlClient.SqlConnection sqlConn;
System.Data.SqlClient.SqlCommand sqlComm;

cm = Dts.Connections["MyAdoManager.conmgr"]; // also tried "MyServerName.MyDbName"

sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
sqlComm = new System.Data.SqlClient.SqlCommand("SELECT * FROM MyTable", sqlConn);
sqlComm.ExecuteNonQuery();

cm.ReleaseConnection(sqlConn);

The issue

After executing package, I always get an exception "Connection not found". How do I get access to the connection used by existing connection manager?

What connection name should I use here cm = Dts.Connections["..."]?

Also, I saw some suggestions that I need connection manager to the script box, but I don't have any connection related settings in the script editor in my SSIS.

Solution

  • Kind of solved. Undocumented feature. Script task can access connection in the connection manager only if they have the same name.

    Once I renamed connection manager from MyAdoManager.conmgr back to MyServerName.MyDbName.conmgr and it matched with the connection name MyServerName.MyDbName I was able to use it in the Script Task.