SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Connect up to a list of servers from a table...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Cowski
Starting Member

USA
29 Posts

Posted - 11/29/2012 :  10:15:09  Show Profile  Send Cowski an ICQ Message  Reply with Quote
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 table
9. 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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/29/2012 :  10:39:58  Show Profile  Reply with Quote
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
Go to Top of Page

Cowski
Starting Member

USA
29 Posts

Posted - 11/29/2012 :  10:48:31  Show Profile  Send Cowski an ICQ Message  Reply with Quote
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!

Edited by - Cowski on 11/29/2012 15:06:14
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/30/2012 :  08:56:17  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/02/2012 :  02:51:44  Show Profile  Reply with Quote
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 iteration
2. 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 list
3. 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 time
4. 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000