Using a existing connection in a Script Component (SSIS)

Question

I have am OLEDB Connection configured in the connection managers and I want to use it in a SCRIPT. The script needs to call a stored proc and then create buffer rows. I have added the connection to the connections available to the script and this is my code.

Boolean fireagain = true;

SqlConnection conn = new SqlConnection();

conn = (SqlConnection)(Connections.Connection
    .AcquireConnection(null) as SqlConnection);


SqlCommand cmd = new SqlCommand();

conn.Open();

ComponentMetaData.FireInformation(
           0, "Script", "Connection Open", string.Empty, 0, ref fireagain);

cmd.Connection = conn;
cmd.CommandText = "up_FullTextParser_select" ;
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("Phrase", DbType.String).Value = Row.Keywords;
cmd.Parameters.AddWithValue("SpecialTerm", DbType.String).Value = "Exact match";
cmd.Parameters.AddWithValue("StopListId", DbType.Int32).Value = 0;
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

while (rdr.Read())
{
    TermsBuffer.AddRow();

    TermsBuffer.Term = rdr[0].ToString();
}

conn.Close();

Anyway, it seems to fail on the AcquireConnection. Am I converting this wrong? Should I be using a different way to using the connections defined outside the script?.

`conn = (SqlConnection)(Connections.Connection .AcquireConnection(null) as SqlConnection);` - This is redundant. With `as`, you are converting the returned object to a `SqlConnection`. You are then casting this `SqlConnection` to the type `SqlConnection`, which it by definition already is, unless it's null, in which case it can't be cast anyway. — Tim M., Feb 27 '18 at 19:33

score 3 · Answer 1 · answered Apr 09 '12 at 02:38

3

You cannot cast an OLEDB connection to SqlConnection object. You must use the OleDbConnection object. See the example - http://blogs.msdn.com/b/mattm/archive/2008/08/22/accessing-oledb-connection-managers-in-a-script.aspx

answered Apr 09 '12 at 02:38

Xavier Abraham

31
2

Also refer this http://stackoverflow.com/questions/11867639/connect-to-sql-database-inside-script-task-in-ssis – Jabez Aug 13 '13 at 08:58
@Jabez links to a question about a Script _Task_. A Script Task is not the same as a Script _Component_. – BigSmoke Mar 14 '18 at 10:59

score 1 · Answer 2 · answered Feb 17 '10 at 12:28

1

This MSDN example implies that you using AcquireConnection incorrectly.

answered Feb 17 '10 at 12:28

Ed Harper

21,127
4
54
80

Those cast simply returns null on my computer :( – Carlo V. Dango Sep 25 '12 at 13:51
The example you link to is for a Script _Task_, not for a Script _Component_. Within a Script Component, the `Dts` object doesn't exist. – BigSmoke Mar 14 '18 at 10:58

score 1 · Answer 3 · answered Feb 17 '10 at 16:14

1

You need to use a managed connection provider.

answered Feb 17 '10 at 16:14

unclepaul84

1,404
8
15

score 1 · Answer 4 · edited Jul 01 '15 at 03:43

If you insist on using an OLEDB connection, you cannot use AcquireConnection but you can extract the connection string and then use it to create an OLEDB connection:

string connstr = Dts.Connections["my_OLEDB_connection"].ConnectionString;
System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(connstr);

This may not work if the connection string is created via an expression of some sort.

score 0 · Answer 5 · answered Apr 16 '21 at 14:24

    IDTSConnectionManager100 connMgr = this.Connections.ADONetAppStaging ; //this we need to give name in connection manager in script component
    SqlConnection myADONETConnection = new SqlConnection();
    myADONETConnection = (SqlConnection)(connMgr.AcquireConnection(null));
    //Read data from table or view to data table
    string query = "Select top 10 * From ##AP_Stagging_Temp_ExportWODuplicates Order by 1,2,3 asc ";
   // string query = "Select  * From ##AP_Stagging_Temp_For_JLL_ExportWODuplicates order by 1,2,3 asc ";
    SqlDataAdapter adapter = new SqlDataAdapter(query, myADONETConnection);
   datatable dtExcelData = new datatable();
    adapter.Fill(dtExcelData);
    myADONETConnection.Close();

Using a existing connection in a Script Component (SSIS)

5 Answers5