Error when Utilizing SSIS ADO.NET Connection from within C# Script Task

Question

I'm getting the error, "The connection was not closed. The connection's current state is open." when executing the conn.Open(); command in the block below in a C# script task in my SSIS package. In Googling, I've seen other people saying that this may result from a try / catch not leading to the conn.Close();, but shouldn't "using" dispose the connection for me when it's finished?

bool fileRecordExists;
using (SqlConnection conn = (SqlConnection)Dts.Connections["connectionName"].AcquireConnection(Dts.Transaction))
{
    SqlCommand sqlCmd = new SqlCommand(queryString, conn);
    conn.Open();
    fileRecordExists = (int)sqlCmd.ExecuteScalar() > 0 ? true : false;
}

Joe C · Accepted Answer · 2016-12-16 19:11:32Z

2

SSIS opens the connection for you. What I usually do, right or wrong, is create a new connection using the connection string which can be acquired very similar to how you are getting the active connection.

I have also, on occasion, used the AcquireConnection method but do not open or close the connection in my scripts.

answered Dec 16, 2016 at 19:11

Joe C

3,9832 gold badges14 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gerald Over a year ago

Thanks, that got me past the first error. But the password for the account we're using is not stored in the connection string - so authentication is failing when trying to open the connection. I'm gonna try acquiring the active connection.

Aashish Jain · Accepted Answer · 2016-12-17 12:59:37Z

1

This will help. The Connection Object is a part of ADO.NET Data Provider and it is a unique session with the Data Source. The Connection Object is Handling the part of physical communication between the C# application(task) and the Data Source.

The Connection Object connect to the specified Data Source and open a connection between the C# application and the Data Source, depends on the parameter specified in the Connection String. When the connection is established, SQL Commands will execute with the help of the Connection Object and retrieve or manipulate data in the Data Source.

Once the Database activity is over, Connection should be closed and release the Data Source resources.

answered Dec 17, 2016 at 12:59

Aashish Jain

1215 bronze badges

2 Comments

Gerald Over a year ago

Thanks for the information! Concerning your last line, do I need to manually release / close the connection? My code is currently as simple as.:

string queryString = "SELECT COUNT(*) FROM table WHERE Filename = '" + filenametoprocess + "'"; SqlConnection conn = (SqlConnection)Dts.Connections["SSISDataConnection"].AcquireConnection(null); SqlCommand sqlCmd = new SqlCommand(queryString, conn); bool fileRecordExists = (int)sqlCmd.ExecuteScalar() > 0 ? true : false;

Should I add conn.Close()? Do I need to release anything?

Aashish Jain Over a year ago

Yes.. That is best practice for any db connection. Closing connection will free up the resource, Every DB has max number of connection open. msdn.microsoft.com/en-us/library/ms187030.aspx

Collectives™ on Stack Overflow

Error when Utilizing SSIS ADO.NET Connection from within C# Script Task

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related