January 15, 2015 at 9:36 am
I have an SSIS package which uses variables and foreach loop containers so connect to multiple instances to retrieve config data. I am adding an extra step to include the port of each instance.
I used a select from a central table to get the connection strings, which is put into [User::Server2]
1) execute SQL task to collect the port
(DECLARE @portNumber NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value = @portNumber OUTPUT
)
Which is then put into a variable @Portnumber
2) I then need to insert this into a certain server with a where clause including another variable
so something like
"INSERT INTO DBO.InstanceConfig VALUES ('"+@[User::Portnumber]+"') where Serverinstance = ('"+@[User::Server2]+"')
but it doesnt work like that.. is there an easier way
January 15, 2015 at 9:57 am
A bit confusing what you are going for here.
Is the table you are inserting into on one server only, with details from all the servers you loop through? Or are you looping through server names and inserting into a table on the current server?
If its the first, you need a field for server name in the table, insert statements don't have where clauses.
If its the second, you need a connection with an expression for connection string or servername linked to your variable, and then you do the insert using that connection.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply