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?

Diedra

Does this answer your question? How do I embed the Connection Manager inside the C# Code of the SSIS Script Component? – Pimp

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);
}

Neelyneeoma

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.

Hern

Thanks for the reply but my main problem is that I dont see the "Dts" object in the class. Even if I include the .dts in the references. – Diedra

Answer is not relevant to Script Component, for which the question is asked. Rather it is about a slightly different Script Task. – Burka

To elaborate on @MaximV.Pavlov 's comment - the question related to a script component (which exists within a Dataflow task), whereas this answer relates to a script task (which exists within the control flow). – Duchamp