Using a existing connection in a Script Component (SSIS)
Solution 1
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
Solution 2
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.
Solution 3
You need to use a managed connection provider.
Solution 4
This MSDN example implies that you using AcquireConnection
incorrectly.
Yup
I'm a Senior Database Developer working for a major online recruitment website. My interests are in all things SQL (MS SQL Server, MySQL, VistaDb) and ASP.NET MVC Development.
Updated on April 17, 2021Comments
-
Yup about 3 years
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?.
-
Carlo V. Dango over 11 yearsThose cast simply returns null on my computer :(
-
Jabez over 10 yearsAlso refer this stackoverflow.com/questions/11867639/…
-
BigSmoke about 6 yearsThe 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 about 6 years@Jabez links to a question about a Script Task. A Script Task is not the same as a Script Component.