C# 在脚本组件 (SSIS) 中使用现有连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2280014/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using a existing connection in a Script Component (SSIS)
提问by Coolcoder
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.
我在连接管理器中配置了 OLEDB 连接，我想在 SCRIPT 中使用它。该脚本需要调用存储过程，然后创建缓冲区行。我已将连接添加到脚本可用的连接中，这是我的代码。
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?.
无论如何，它似乎在 AcquireConnection 上失败了。我转换这个错误吗？我应该使用不同的方式来使用脚本外定义的连接吗？
回答by Ed Harper
This MSDN exampleimplies that you using AcquireConnectionincorrectly.
这个 MSDN 示例暗示您使用AcquireConnection不正确。
回答by unclepaul84
You need to use a managed connection provider.
您需要使用托管连接提供程序。
回答by Xavier Abraham
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
不能将 OLEDB 连接强制转换为 SqlConnection 对象。您必须使用 OleDbConnection 对象。请参阅示例 - http://blogs.msdn.com/b/mattm/archive/2008/08/22/accessing-oledb-connection-managers-in-a-script.aspx
回答by Thomas Gabriel
If you insist on using an OLEDB connection, you cannot use AcquireConnectionbut you can extract the connection string and then use it to create an OLEDB connection:
如果坚持使用 OLEDB 连接，则不能使用，AcquireConnection但可以提取连接字符串，然后使用它来创建 OLEDB 连接：
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.
如果连接字符串是通过某种表达式创建的，这可能不起作用。
