SSIS Script Component connection

Question

I've been searching for a solution for days now and I still cant seem to find one. I have a problem acquiring a connection in my Script component. I need to query my database to retrieve an Id to be used before I insert it in the

public override void AcquireConnections(object Transaction)
{
    connMgr = base.Connections.Connection;
    conn =  (SqlConnection)connMgr.AcquireConnection(null);
}

I get an exception here.

System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

Any solutions?

Answer 1

For those that want to be able to do this in a Script Component:

1. Double Click the Script component to open the "Script Transformation Editor"
2. Click the "Connection Managers" list item.
3. Add a new Connection Manager. Select an existing ADO.NET connection manager.
4. Click on the "Script" list item and then the "Edit Script..." button.

You can do something like this inside your script:

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 2

ADO.NET connection manger should be created and refer into the code to type cast to the SqlConnection. If you dont have the ADO.NET connection in your SSIS pakcage you will get the TypeCast exception. Following steps should be used if you want to use the SqlConnection.

1. Create the ADO.NET connection.

2. Use the following line in your code.

   var connObj = Dts.Connections["ADO.NETConnectionName"].AcquireConnection(null);
   
   var sqlConn = (SqlConnection)connObj;
   

3. Once you done with your SQL connection. Use the following code to Close/ Release your connection.

   Dts.Connections["ADO.NETConnectionName"].ReleaseConnection(connObj);
   

Hope this helps.