Using a existing connection in a Script Component (SSIS)

Question

I have am OLEDB Connection configured in the connection managers and I want to use it in a SCRIPT. The script needs to call a stored proc and then create buffer rows. I have added the connection to the connections available to the script and this is my code.

Boolean fireagain = true;

SqlConnection conn = new SqlConnection();

conn = (SqlConnection)(Connections.Connection
    .AcquireConnection(null) as SqlConnection);


SqlCommand cmd = new SqlCommand();

conn.Open();

ComponentMetaData.FireInformation(
           0, "Script", "Connection Open", string.Empty, 0, ref fireagain);

cmd.Connection = conn;
cmd.CommandText = "up_FullTextParser_select" ;
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("Phrase", DbType.String).Value = Row.Keywords;
cmd.Parameters.AddWithValue("SpecialTerm", DbType.String).Value = "Exact match";
cmd.Parameters.AddWithValue("StopListId", DbType.Int32).Value = 0;
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

while (rdr.Read())
{
    TermsBuffer.AddRow();

    TermsBuffer.Term = rdr[0].ToString();
}

conn.Close();

Anyway, it seems to fail on the AcquireConnection. Am I converting this wrong? Should I be using a different way to using the connections defined outside the script?.

Answer 1

This MSDN example implies that you using AcquireConnection incorrectly.

Answer 2

You need to use a managed connection provider.