Script Component Connection Management

Hello,

Greetings, and Happy Friday. I want to check myself in regards to the proper use of connections in a data flow script component being used as a transformation. I want to ensure that the solution is ideal from a performance standpoint, but more importantly that there won't be any resource leaks from connections that are not being closed or objects that are not being disposed properly.

This transformation script component is writing to a table based on the rows that pass through Input0_ProcessInputRow(). What I'm doing so far is, in the script component script, I've declared a class-level variable of type System.Data.SqlClient.SqlConnection. In the PreExecute() event handler I am initializing this connection with the following code (which comes after the call to MyBase.PreExecute()):

Dim connMgr As IDTSConnectionManager90 connMgr = Me.Connections.JobDB adoConn = CType(connMgr.AcquireConnection(Nothing), SqlClient.SqlConnection) If adoConn.State <> ConnectionState.Open Then adoConn.Open() End If connMgr = Nothing
The adoConn variable referred to there is the class-level variable. So, my first questions:

Is this the best way to initialize the connection? And is that code checking adoConn.State necessary? Can I pretty much assume that the connection manager is going to give me an open connection?

Then in the PostExecute() event handler I have this code (again, after the MyBase call):

If Not (adoConn Is Nothing) Then
If adoConn.State = ConnectionState.Open Then
adoConn.Close()
End If
adoConn.Dispose()
adoConn = Nothing
End If

Next questions:

Is this adoConn variable actually holding a reference to an object whose primary owner is in the connection manager? Is it necessary to close my SqlConnection this way, or is that going to actually close the connection manager's connection?

I'm also wondering whether I need to have this same code in the script component's Finalize() handler also...if there's an error I don't want the resource to leak. Or will PostExecute() fire even if there's an error in Input0_ProcessInputRow()?

Thanks in advance,
Dan