在 SSIS 中的脚本任务中连接到 SQL 数据库 [英] Connect to SQL database inside Script Task in SSIS

问题描述

在 SSIS 中的脚本任务内部，我需要调用 SQL 数据库.我有一个在将数据库添加到数据源文件夹时创建的连接字符串，但是现在我不确定如何在 C# 代码中引用它.我知道如何在 ASP 网站背后的代码中执行此操作，但是 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.

编辑

这行代码实际上最终抛出了一个异常:

This line of code actually winds up throwing an exception:

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

它写着:无法将类型为‘System._ComObject’的 COM 对象转换为类类型‘System.Data.SqlClient.SqlConection’."

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

推荐答案

您不能在脚本任务中使用连接管理器的配置，例如:conctionManager1.exceuteSQLStatment(...)

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

一旦你进入脚本任务的内部"，你就需要像访问变量一样访问 CM:

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

这篇关于在 SSIS 中的脚本任务中连接到 SQL 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！