Accessing Global Variables in an ExecuteSQL Task
I have an SSIS package that does the following: Selects connection strings from a server table. The connection string is the server name along with the domain (i.e. Dalin.myhouse.com) or the direct IP address of the server.
The package iterates over each connection string and populates a specific global variable. This variable was created using the Variable constructor. For each connection string, the package will receive information about the server.
The problem is that when you add the server IP to the list, the "friendly name" may not be known at the time, so I would just get the server IP in both the connection string column and the friendly name column of the table. I want to update the "Friendly Name" column of the server record in this table after the end of the iteration so that it has the server name inferred from the server using SERVERPROPERTY ("Servername")
To do this, I would like to use ExecuteSQL Task with the following code:
UPDATE [myDB].[mySchema].[myServers]
SET [ServerName] = VarA
WHERE ConnectionString = VarB
The previous code uses a static connection to the server that hosts the myServers table.
VarA represents the global value I want to set for ServerName, which will be set in a separate SQLTask using SERVERPROPERTY ('Servername'). It should be in a separate task because it will have to connect to the server using the same server that the current iteration is using.
VarB is set at the beginning of each iteration to the next Connection line in the list.
I've seen examples of using this for Script Task and Script Components, but I would like to just use ExecuteSQL Task to accomplish this.
Summary:
Connect to ServerA and fill in the two global variables.
Connect to ServerB and use two global variables to update a specific row in the table.
Any ideas?
source to share
I don't see how this can be done without the variables set in the Script task, since the ExecuteSQL tasks must be set to connect to the database. Script Tasks work for this because their connection is in the context of the server that runs them. That being said, you can use a Script task in front of this ExecuteSQL task, which sets variables to the local server instance.
source to share
So you need an Execute SQL task to accept parameters?
http://msdn.microsoft.com/en-us/library/ms187685.aspx
I may have misunderstood ...
source to share