Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
371 views
in Technique[技术] by (71.8m points)

c# - Connect to SQL database inside Script Task in SSIS

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...