Cannot connect to data source SSIS script task

Question

I cannot seem to connect to the database connection i have set up in the SSIS package inside a script task. Here is my code it is a OleDB connection.

public void Main()
    {
        // TODO: Add your code here
        OleDbConnection myOleDbConnection = new OleDbConnection();
        myOleDbConnection = (OleDbConnection)(Dts.Connections["Connection"].AcquireConnection(Dts.Transaction) as OleDbConnection);
        MessageBox.Show(myOleDbConnection.ConnectionString, "OleDB Connection");

        Dts.TaskResult = (int)ScriptResults.Success;
    }

I get the following exception

Answer 1

AcquireConnection() method of the connection manager returns a unmanaged Object, in your case of Oledb it returns native COM object. so use Ado.net connection if possible in your scenario.

If you want to stick to Oledb then here is a workaround.

ConnectionManager cm = Dts.Connections["Connection"];
IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as  
                                                      IDTSConnectionManagerDatabaseParameters100;
OleDbConnection myOleDbConnection = cmParams.GetConnectionForSchema() as OleDbConnection;