How to access to existing ADO.NET Connection Manager within data flow script component

Question

I want to execute SQL statement for each row in my Data Flow, so I use script component (not script task) in data flow. I tried code like below, but it doesn't compile.

using (SqlConnection connection = this.Connections.Connection.AcquireConnection(null) as SqlConnection)
{
    using (SqlCommand command = connection.CreateCommand())
    {
        command.CommandText = "SELECT [Value] FROM dbo.MyTable";
        command.CommandType = CommandType.Text;

        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                ProfanityWords.Add(reader.GetValue(0).ToString());
            }
        }
    }

    this.Connections.Connection.ReleaseConnection(connection);
}

Answer 1

Did you add a reference to the connection manager on the Script Component? This can be done by adding the ADO.NET connection manager on the Connection Managers tab of the Script Component. After this, it can be accessed as follows. The example below is a modified version of your code, with MyConnection as the name given to the connection manager on the Script Component GUI.

using (SqlConnection connection = this.Connections.MyConnection.AcquireConnection(null) as SqlConnection)
{
    using (SqlCommand command = connection.CreateCommand())
    {
        command.CommandText = "SELECT [Value] FROM dbo.MyTable";
        command.CommandType = CommandType.Text;

        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                ProfanityWords.Add(reader.GetValue(0).ToString());
            }
        }
    }

    this.Connections.MyConnection.ReleaseConnection(connection);
}