21

Inside of a Script Task in SSIS, I need to make a call to an SQL database. I have a connection string that was created when I added the database to the data sources folder, however now I'm not sure how to reference it inside the C# code. I know how to do this in the code behind of an ASP website, however it seems that SSIS should have a more direct method.

EDIT

This line of code actually winds up throwing an exception: 

sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);

It reads: "Unable to cast COM object of type 'System._ComObject' to class type 'System.Data.SqlClient.SqlConection.'"

Agnel Amodia
  • 765
  • 8
  • 18
NealR
  • 10,189
  • 61
  • 159
  • 299

2 Answers2

27

you cant use the configurations from a connection manager from inside a script task like: conectionManager1.exceuteSQLStatment(...)

once you are "inside" the script task you need to access the CM like a variable:

ConnectionManager cm;
System.Data.SqlClient.SqlConnection sqlConn;
System.Data.SqlClient.SqlCommand sqlComm;

cm = Dts.Connections["conectionManager1"];

sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
sqlComm = new System.Data.SqlClient.SqlCommand("your SQL Command", sqlConn);
sqlComm.ExecuteNonQuery();

cm.ReleaseConnection(sqlConn);
Diego
  • 34,802
  • 21
  • 91
  • 134
  • If I wanted to get a value (in my case I'm trying to get a DateTime) out of the sqlComm, would I just use something like: DbDataReader reader = sqlComm.ExecuteReader() - and then cycle through the whatever comes out? – NealR Aug 08 '12 at 15:28
  • 2
    By changing the database connection from OLEDB to ADO.Net, the code listed above worked for me. – Tequila Jun 28 '13 at 13:50
  • @Tequila. You would have to use Oledb objects to connect using an Oledb connection eg..System.Data.OleDbOleDbConnection and OleDbCommand... – Martin MacPherson Oct 11 '13 at 09:00
  • @Diego - You deserve a gold medal for this post. This saved me tons of time. Btw, where can I get information about these kind of things ? Was this thing mentioned in some MS documentation ? – Steam Nov 18 '13 at 19:58
  • @Tequila - That is correct. That is exactly what is needed. Looks like OLEDB will not work here. – Steam Nov 18 '13 at 20:00
  • Wbere do you get Dts.Transaction from? It does not seem to be available in my project @Diego. – M Akin Dec 17 '14 at 21:45
  • 2
    Note this **will not** work with OLEDB, per the Microsoft Docs, and all of this is documented here: https://learn.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/task/connecting-to-data-sources-in-the-script-task – sorrell Jan 12 '18 at 16:04
  • @MartinMacPherson - trying to cast the rawConnection (after `AcquireConnection`) to OleDbConnection throws an exception. Have you tried that and have it work in this context? Your comment seems wrong. – Don Cheadle Sep 27 '19 at 16:17
  • I'm connecting to ASE (Adaptive Server Enterprise) using ado.net but going through odbc since the ado.net driver isn't exposed in SSIS connection wizard. One thing I learned was to make sure you use the exact name of the connection (including spaces) or your connection manager won't be found in your script task. – Mark Libner Aug 24 '20 at 19:43
1

You must check the privider of the connection that your are trying to instantiate.

You can't cast a OleDb.OleDbConnection connection as a SQLClient.SQLConnection, the parameters are different.

Hugo Vares
  • 977
  • 7
  • 7