Looping Thru Server Names in SSIS Package
-
hi experts,
I have a table that contains 1 column - ServerName varchar(50). It contains 40 rows or 40 unique server names.
My SSIS package loops thru each server name storing the name into a variable named @server.
** This is the part I'm having trouble with: My next task is to use that @server variable value and construct a TSQL stmt that will read from that servers' master database.
I suppose it would look something like this:
Select * from @ServerName.master.dbo.sysjobs for example. Then return to the ForEach loop to get the next server name.
My question is Can I do this in a 2008 SSIS package? Can anyone point me to an example please? I know this is fairly easy to do.
Thanks for any tips.
-
dbaforever (10/11/2012)
hi experts,I have a table that contains 1 column - ServerName varchar(50). It contains 40 rows or 40 unique server names.
My SSIS package loops thru each server name storing the name into a variable named @server.
** This is the part I'm having trouble with: My next task is to use that @server variable value and construct a TSQL stmt that will read from that servers' master database.
I suppose it would look something like this:
Select * from @ServerName.master.dbo.sysjobs for example. Then return to the ForEach loop to get the next server name.
My question is Can I do this in a 2008 SSIS package? Can anyone point me to an example please? I know this is fairly easy to do.
Thanks for any tips.
Yes, this can be done. You will need to create a new connection manager (this can be a connection to any server initially). Here is an example of one I'm using at the moment:
You will then need to modify the connection and add an expression like the below:
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Does that work when itterating through a record set of server names, as I thought that the connections were set only when the package was loaded and couldnt change during execution.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Jason-299789 (10/11/2012)
Does that work when itterating through a record set of server names, as I thought that the connections were set only when the package was loaded and couldnt change during execution.This works really well as long as the Execute SQL Task component is using the dynamic connection as in the second screenshot above. I took these screenshots from my own package which works like a charm.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Abu Dina (10/11/2012)
Jason-299789 (10/11/2012)
Does that work when itterating through a record set of server names, as I thought that the connections were set only when the package was loaded and couldnt change during execution.This works really well as long as the Execute SQL Task component is using the dynamic connection as in the second screenshot above. I took these screenshots from my own package which works like a charm.
Interesting, as i've tried this in the and had problems using a dynamic connector within the same package as the variable, and had to resort to using a Parent/Child package arangement.
I'll put a test rig together to see if thats changed.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Thank you Jason and Abu! I will explore your ideas.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply