CODEWITHSUNDEEP
vb.netsql-server-2008ssis
Santhosh_ms3
Santhosh_ms3

Reputation: 110

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

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)

Upvotes: 1

Views: 12103

Answers (3)

Fernando Sarmiento
Fernando Sarmiento

Reputation: 11

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

Upvotes: -1

cfrag
cfrag

Reputation: 746

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

Upvotes: 1

billinkc
billinkc

Reputation: 61211

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

Upvotes: 1

Related Questions