Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Cowski
Starting Member
30 Posts |
 Posted - 2012-11-29 : 10:15:09
|
Greetings!I’m hoping what I’m trying to accomplish is doable & not too difficult to achieve by using SSIS.I have table with SQL server names in it. I’ll call it “ServerList”. I can add more info to this table if required but right now it only has a SQL Server server names in it. For example purposes we’ll call them DevSQLSvr01, DevSQLSvr02, ProdSQLSvr01, ProdSQLSvr02.What I would like my SSIS package to do is to loop through this table “ServerList”, connect up to that particular server, execute some t-sql code (for example, pull a database list on that particular server) then insert those results into a repository on the local server. Loop, repeat. So in theory it would do the following:1. Read the server list. 2. Connect up to DevSQLSvr01.3. Run t-sql code to pull a database list.4. Insert that data into a local table.5. Read the server list.6. Connect up to DevSQLSvr02.7. Run t-sql code to pull a database list.8. Insert that data into a local table9. Continue on.I want to do it this way so if I add/update/delete a server, I change the info in this “ServerList” table & that’s all. Right now I have the job running on every single server pushing the data to my repository. If I could have 1 SSIS package on my reporting server PULL the data, that would be 1 job to worry about, not 1 job on every server.I know I have to think about the connection manager. But as powerful as SSIS is, I would think there would be a way to do this.I hope I explained this well enough. Any suggestions?Thanks!  |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-29 : 10:39:58
|
| You can use a Foreach Loop Container with a Foreach ADO Enumerator to get the servernames from your control server, and then use the servername in the expression for the source connection string to connect to each of the servers listed in your control table.This page has an example with screen shots etc. - but it is a little dated, probably SSIS 2005; so the screens in 2008 and 2012 may look slightly different, but the mechanics of it should be pretty much the same: http://www.codeproject.com/Articles/14341/Using-the-Foreach-ADO-Enumerator-in-SSIS |
 |
|
|
Cowski
Starting Member
30 Posts |
Posted - 2012-11-29 : 10:48:31
|
This looks perfect!!  Thank you for the link! I'll start going through this to see what is what. Will keep everyone posted!--==[ UPDATE!! ]==--It's been a few hours since I checked that link. In theory it's doing pretty much what I'm looking for but the author didn't explain a few things and I'm at a dead end again. I tried his sample in both SSMS 2008 & 2005 and netted the same results. Issues & errors with the OLE DB connection object inside the ForEach Loop container. Works fine until I add an expression to it like the example says then it breaks requiring me to delete the connection in the connection manager and try again. I'm following the example to the letter and can't figure out.I'm still plugging away though. Thanks again. Hopefully get this fixed soon! Getting a headache from all this!  |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-30 : 08:56:17
|
| Sorry to hear you are having difficulties. There are some user comments and the problems they ran into and solutions in the comments section of that article. Does any of that help? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-02 : 02:51:44
|
quote:
Originally posted by Cowski
This looks perfect!! Thank you for the link! I'll start going through this to see what is what. Will keep everyone posted!--==[ UPDATE!! ]==--It's been a few hours since I checked that link. In theory it's doing pretty much what I'm looking for but the author didn't explain a few things and I'm at a dead end again. I tried his sample in both SSMS 2008 & 2005 and netted the same results. Issues & errors with the OLE DB connection object inside the ForEach Loop container. Works fine until I add an expression to it like the example says then it breaks requiring me to delete the connection in the connection manager and try again. I'm following the example to the letter and can't figure out.I'm still plugging away though. Thanks again. Hopefully get this fixed soon! Getting a headache from all this!
well this is what you should be doing (i didnt check link but i'm giving you steps myself)1. Add a object type variable in SSIS package. Add also a string variable to get individual values during iteration2. Add an execute sql task with SELECT query to retrieve server list and then map it to object variable created to get it populated by server list3. Add a FOR EACH LOOP container based on ADO .NET variable and map it to above variable. Inside that map the other string variable to retrieve servername each time4. Inside loop add a execute sql task to retrieve the db list from table. If table name and databasenames are same in all server you can use static query else you can use expression builder to build query dynamically based on table and dbname which you should have stored in some table.5. In the connection manager properties set an expression for server name and map it to string variable used. This will ensure query is run against correct server each time------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Connect up to a list of servers from a table...