Reusing connection manager from SSIS script task

Question

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.

score 0 · Accepted Answer · answered Dec 11 '20 at 23:33

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.

Reusing connection manager from SSIS script task

1 Answers1