Accessing 'Global' Variables In An ExecuteSQL Task

Question

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.

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.

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

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

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.

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.

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

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.

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?

Answer 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.

Answer 2

So you need the Execute SQL task to take parameters?

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

Maybe I've misunderstood...