SSIS连接管理器不使用ConnectionString值

问题描述

我有一个子包，其中连接管理器的ConnectionString属性由父包变量配置设置。我设置了一个脚本任务，该脚本任务将在数据流任务之前显示一个带有ConnectionString属性值的消息框。

 ` MessageBox.Show（Dts.Connections [ CPU _ *]。ConnectionString.ToString（））;`
  

当我运行父包时，消息框显示连接字符串在每次迭代中都在变化，但是在数据流中，它总是从相同的源绘制数据。

我正在使用SQL Server 2008 R2，连接管理器是ADO.Net类型，RetainSameConnection设置为False，并且我已经研究了好几天。有人有任何想法吗？

更新（2/23/2015）：为了使这个陌生人，当我查看诊断日志时，他们告诉我，当建立新连接时

解决方案

我在这里找到了答案。我尚不确定较新的版本，但是直到2008R2为止，当您将连接字符串传递到子程序包中时，都存在一个错误。正如您正确指出的那样，传递了连接字符串IS，但是在传递父配置之后评估连接是在父配置之前进行，还是在设计对象中更新了连接字符串。

这两种方法都不起作用。

如果像我一样，您不想向所有包中添加其他脚本任务，将需要执行以下操作：

父母套餐

• 假设我们正在使用称为 MyDBConnection
的OLE DB连接

• 添加一个字符串变量来保存连接字符串（ MyConnection ）



• 添加以下行的C＃脚本任务（在Package Task之前）：

  
  
  

  Dts.Variables [ User :: MyConnection]。Value = Dts.Connections [ MyDBConnection]。ConnectionString;

子程序包

• 添加一个字符串变量来保存conne字符串（ MyConnection ）


• 添加父包配置以传递 MyConnection


• 在OLE DB连接的属性中添加一个表达式，以从 MyConnection

更新Connection String属性。

I have a child package where the ConnectionString property of a Connection Manager is set by a Parent Package Variable Configuration. I set up a script task that brings up a message box with the value of the ConnectionString property right before the dataflow task.

`MessageBox.Show(Dts.Connections["CPU_*"].ConnectionString.ToString());`

When I run the parent package, the message box shows that the connection string is changing with every iteration, but in the dataflow it always draws the data from the same source.

I'm using SQL Server 2008 R2, the connection manager is an ADO.Net type, RetainSameConnection is set to False, and I've been researching this for days. Anybody have any ideas?

Update (2/23/2015): To make this stranger, when I look at the diagnostic logs, they tell me that when the new connections are being opened they are using the new connection strings.

解决方案

I found an answer here Passing SSIS Connection String in parent variable works but package still validates against child design value.

I'm not sure about later versions yet, but certainly up to 2008R2 there is a bug when you pass a connection string into a child package. As you correctly point out the connection string IS passed, but either the connection is evaluated prior to the parent configuration or the connection string is updated from the design object after the parent configuration has been passed.

Either way it just doesn't work.

If, like me, you don't want to add an additional Script Task to all your packages you will need to do the following:

Parent Package

• Lets assume we are using a OLE DB connection called MyDBConnection
• Add a string variable to hold the connection string (MyConnection)

• Add a C# Script Task (before the Package Task) with the line:

  Dts.Variables["User::MyConnection"].Value = Dts.Connections["MyDBConnection"].ConnectionString;

Child Package(s)

• Add a string variable to hold the connection string (MyConnection)
• Add a parent package configuration to pass the value of MyConnection
• In the properties for the OLE DB connection add an expression to update the Connection String property from MyConnection

这篇关于SSIS连接管理器不使用ConnectionString值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！