CODEWITHSUNDEEP
c#sql-serverssisssis-2012
wp78de
wp78de

Reputation: 18980

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

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.

Upvotes: 1

Views: 5724

Answers (1)

Ferdipux
Ferdipux

Reputation: 5256

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.

Upvotes: 2

Related Questions