Establishing the connection from Script Task(VB) to OLEDB Connection Manager of SSIS Package

Question

How to use the existing OLEDB connection Manager from scripttask Task(VB). i need to execute a SQL statement from "Script Task" Task.

Edit

Attempted the following code but couldn't get the connection with my OLEDB Connectionmanager

Dim MyCM As ConnectionManager = Dts.Connections("MyConnection1") 
Dim CS As String = Dts.Connections(MyCM).ConnectionString 
sqlConn = New SqlConnection(CS)

score 1 · Accepted Answer · answered Feb 01 '12 at 14:15

1

I would refer you to this most excellent blog post by Todd McDermid, Using Connections Properly in an SSIS Script Task

answered Feb 01 '12 at 14:15

billinkc

59,250
9
102
159

Awesome!. Dim MyCM As ConnectionManager = Dts.Connections("MyConnection1") Dim CS As String = Dts.Connections(MyCM).ConnectionString sqlConn = New SqlConnection(CS) I tried like this, is there anything wrong. i couldnt get the connection with my OLEDB Connectionmanager. – Santhosh_ms3 Feb 01 '12 at 18:32
What is the error you are receiving. Couldn't connect does not tell me anything – billinkc Feb 01 '12 at 21:50
I managed it in someother way. Anyway thanks for sharing article. – Santhosh_ms3 Feb 02 '12 at 09:22

score 1 · Answer 2 · answered Feb 01 '12 at 14:18

1

First you must add the connection manager to the script box:

Adding a connection manager to a script component

Then, you access it in the code through the Connections object, e.g:

this.Connections.EBIC;

Check the link in the answer from user bilinkc for more detailed information

answered Feb 01 '12 at 14:18

cfrag

726
6
12

score -1 · Answer 3 · answered Mar 03 '14 at 18:12

I don't remember the link where I found this. but it woks! (only you have to change to C# or find the way to put in VB it coud be easy) SSIS has problems with cast Object_COM to IDTSConnectionManager, I wasted a lot of time with it, so OleDbConnection fix it.

Using your Connection Manager and the name of the connection OLEDB_NameConnectionTo_SQLServer, if you use storesProcedures or function in the query the character "?" is important to put the parameters after, I really don't know why so off the hook

you have to add this refences.

using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.OleDb; ...

try{

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

    OleDbCommand cmd = new OleDbCommand("EXEC SP ?, ?, ?;", conn);

    OleDbParameter Param = cmd.Parameters.Add("@PIParam", OleDbType.Integer);
    ParametroId.Value = 3;

    OleDbParameter Cons = cmd.Parameters.Add("@PICons", OleDbType.Integer);
    Cons.Value = 2;

    OleDbParameter Cte = cmd.Parameters.Add("@PICte", OleDbType.Integer);
    Cte.Value = 1;

    using ( OleDbDataReader reader = cmd.ExecuteReader()  ){
    if ( reader.HasRows && reader.Read() )
        valor = (reader["VALUE"].ToString());
    }

    cm.ReleaseConnection(conn);

} catch( Exception ex ){
    MessageBox.Show(ex.Message.ToString());
}

Establishing the connection from Script Task(VB) to OLEDB Connection Manager of SSIS Package

Edit

3 Answers3

Linked