在ExecuteSQL任务中访问“全局”变量

I have an SSIS package that does the following: Selects the connection strings from a table of servers. The connection string is either the name of the server along with the domain (i.e. Dalin.myhouse.com) or is the direct IP to a server.

我有一个SSIS包,它执行以下操作:从服务器表中选择连接字符串。连接字符串是服务器的名称和域(即Dalin.myhouse.com),或者是服务器的直接IP。

The package iterates through each connection string and populates a defined 'global' variable. This variable was created with in the Variable designer. For each connection string, the package will retrieve information about the server.

包迭代每个连接字符串并填充定义的“全局”变量。此变量是在Variable设计器中创建的。对于每个连接字符串,包将检索有关服务器的信息。

The problem I'm having is when you add a server IP to the list, the 'Friendly Name' may not be known at that time so I would just have the server IP in both the connection string column and the friendly name column of the table. I want to, after the end of an iteration, update that the Friendly Name column of the server entry within that table so that it has the server name pulled from the server using SERVERPROPERTY('Servername')

我遇到的问题是当你向列表添加服务器IP时,当时可能不知道'友好名称',所以我只在连接字符串列和友好名称列中都有服务器IP表。我希望,在迭代结束后,更新该表中服务器条目的Friendly Name列,以便它使用SERVERPROPERTY('Servername')从服务器中提取服务器名称

To do this, I would like to use an ExecuteSQL Task with the following code:

为此,我想使用以下代码的ExecuteSQL任务:

UPDATE [myDB].[mySchema].[myServers]
SET [ServerName] = VarA
WHERE ConnectionString = VarB

The previous code is using a static connection to the server where the myServers table resides.

前面的代码使用静态连接到myServers表所在的服务器。

VarA represents the global value I want to set the ServerName to which would be set in a separate SQLTask by using SERVERPROPERTY('Servername'). It needs to be in a separate task because it would have to connect to a server by using the same server the current iteration is using.

VarA表示我想通过使用SERVERPROPERTY('Servername')将ServerName设置为在单独的SQLTask中设置的全局值。它需要处于单独的任务中,因为它必须使用当前迭代使用的同一服务器连接到服务器。

VarB is set at the beginning of every iteration to the next Connection String in the list.

VarB在每次迭代开始时设置为列表中的下一个连接字符串。

I've seen examples on how to use this for the Script Task and Script Components but I would like to simply use the ExecuteSQL Task to accomplish this.

我已经看到了如何将它用于脚本任务和脚本组件的示例,但我想简单地使用ExecuteSQL任务来完成此任务。

Summary:
Connect to ServerA and fill two global variables.
Connect to ServerB and use the two global variables to update a specific row in a table.

Any ideas?

摘要:连接到ServerA并填充两个全局变量。连接到ServerB并使用两个全局变量更新表中的特定行。有任何想法吗?

2 个解决方案

#1

I can't see how this can be accomplished without the variables being set within a Script Task, since ExecuteSQL tasks have to be set to a database connection. Script Tasks work for this because their connection is within the context of the server that's executing them. That being said, you could use a Script Task prior to this ExecuteSQL task that sets the variables to the local server instance.

如果没有在脚本任务中设置变量,我无法看到如何实现这一点,因为必须将ExecuteSQL任务设置为数据库连接。脚本任务适用于此,因为它们的连接位于正在执行它们的服务器的上下文中。话虽这么说,您可以在此ExecuteSQL任务之前使用脚本任务,将任务设置为本地服务器实例。

#2

So you need the Execute SQL task to take parameters?

那么您需要执行SQL任务来获取参数吗?

http://msdn.microsoft.com/en-us/library/ms187685.aspx

Maybe I've misunderstood...

也许我误解了......

#1

#2