Modern way connect to SQL Server inside Script Task in SSIS 2012 and later

Question

When you search the internet or SO how to connect to SQL database inside Script Task in SSIS you will find .NET v1.1ish code like this:

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

cm = Dts.Connections["ADO.NET.SqlDB"];    
sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);

sqlComm = new System.Data.SqlClient.SqlCommand("your SQL Command", sqlConn);
sqlComm.ExecuteNonQuery();

cm.ReleaseConnection(sqlConn);

I am looking for updated code that makes good use of later introduced .NET features.

For a start, how about the code below. Is this the current recommended way to connect to SQL Server inside Script Task in SSIS 2012 and later or do I miss something here?

ConnectionManager cm = Dts.Connections["ADO.NET.SqlDB"];
using (var sqlConn = (SqlConnection)cm.AcquireConnection(Dts.Transaction))
{
    if (sqlConn.State != ConnectionState.Open)
        sqlConn.Open();

    using (var sqlComm = new SqlCommand(
        String.Format("UPDATE myTab SET Processed = 4 where ID = '{0}'",
        idNumber), sqlConn))
    {
        return sqlComm.ExecuteNonQuery();
    }
}

Is the ReleaseConnection() still needed? Is sqlConn.Open() really needed in an SSIS context?

One year later, and hopefully a little wiser, I settled with code like this:

ConnectionManager cm = Dts.Connections["ADO.NET.SqlServerDB"];
var sqlConn = (SqlConnection)cm.AcquireConnection(Dts.Transaction);
using (var sqlCmd = new SqlCommand(
  "INSERT INTO apou_moodle_import(id_num, username, email) VALUES(@IdNum, @Username, @Email)",
  sqlConn))
{
    sqlCmd.CommandType = CommandType.Text;
    sqlCmd.Parameters.AddWithValue("@IdNum", newUser.id_num);
    sqlCmd.Parameters.AddWithValue("@Username", newUser.username);
    sqlCmd.Parameters.AddWithValue("@Email", newUser.email);

    int rowsAffected = sqlCmd.ExecuteNonQuery();
}
cm.ReleaseConnection(sqlConn);

So, I keep using ConnectionManager.ReleaseConnection, however, SqlConnection.Open & Close are not needed in an SSIS context. Plus, use Parameters to play safe.

In that particular code, `sqlConn` would never already be open. But it doesn't hurt. I guess this is a trivial example right? In these examples there is no reason to use a script task, you can just use a SQL Task instead — Nick.Mc, Dec 27 '17 at 05:13
Of course, it's just a simple example. Imagine a number of functions that do Selects, Updates, Inputs, etc. — wp78de, Dec 27 '17 at 05:44
In my experience this is better implemented in a stored procedure than in a SSIS script task. — Nick.Mc, Dec 27 '17 at 05:45
Imagine a package that does REST calls, serializes objects, compress images, and other stuff that is hard to achieve in a stored procedure. — wp78de, Dec 27 '17 at 05:49
Seems like a reasonable use for a script task, but again it would probably be better suited in a console app. Can you tell I don't like script tasks? :) — Nick.Mc, Dec 27 '17 at 05:50
I see your point but a Script Task has some virtues too, eg. you can use the DTS log, access package variables, easy deployment, etc. Anyways, this not an XY problem question. :) — wp78de, Dec 27 '17 at 06:13
I tend to use the OleDB connection as it is the same as the SQL connection manager in the application. Furthermore, I set the connection string from config file to both the CM and to a variable if the package uses a script task. And I use the variable in the script task instead of calling CM. Using the config allows for switching environments (DEV, TEST, PROD) with just a config update. — KeithL, Dec 28 '17 at 15:53

score 2 · Accepted Answer · answered Dec 28 '17 at 09:34

Well, using structure allows you to automate disposing variables and handle it better. However, sqlConn is not a simple class, it is a ConnectionManager instance. When you start using it, you call AcquireConnection, when end - call ReleaseConnection. The ReleaseConnection may perform some housekeeping specific to this Data Source. Depending on ConnectionManager implementation, it may check at disposal whether ReleaseConnection was called or not and call it.

To my understanding, your code with using should be Ok in most cases. Problems may arise when you repeatedly open connections and do not release it - you might run of connection pool etc. I would wrap internals into try - finally block to ensure ReleaseConnection execution.

Modern way connect to SQL Server inside Script Task in SSIS 2012 and later

1 Answers1