Connect to SQL database inside Script Task in SSIS

Question

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.'"

Diego · Accepted Answer · 2012-08-08 15:21:01Z

29

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

answered Aug 8, 2012 at 15:21

Diego

36.5k21 gold badges94 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

NealR Over a year ago

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?

Tequila Over a year ago

By changing the database connection from OLEDB to ADO.Net, the code listed above worked for me.

Martin MacPherson Over a year ago

@Tequila. You would have to use Oledb objects to connect using an Oledb connection eg..System.Data.OleDbOleDbConnection and OleDbCommand...

Steam Over a year ago

@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 ?

sorrell Over a year ago

Note this will not work with OLEDB, per the Microsoft Docs, and all of this is documented here: learn.microsoft.com/en-us/sql/integration-services/…

|

Hugo Vares · Accepted Answer · 2019-12-03 12:57:33Z

3

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.

edited Dec 3, 2019 at 12:57

answered Dec 1, 2019 at 2:15

Hugo Vares

1,1678 silver badges7 bronze badges

Collectives™ on Stack Overflow

Connect to SQL database inside Script Task in SSIS

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related