I am confused to the point of extreme frustration and thinking about
having one of our .NET developers just write a custom application to do
what I need. To give you more background, we run a 3rd party piece of
software in a 5-7 Microsoft SQL server environment. I want to extract
data from these 5-7 servers into a warehouse location. Sounds simple
enough right? I'd prefer to have this read the list of servers from
a table in the warehouse location because doing this any other way
seems like it would be extremely high maintenance. At that point, I
would want it to loop through the list of servers and retrieve a list
of databases for that server. Once the list of databases for each
server has been retrieved, I'd like to actually query each specific
database and send data back to the warehouse noting exactly where the
data came from (server and database). Is this possible to achieve from
Integration Services or am I missing something or what? I am
relatively new to SSIS, but I would think some kind of functionality
such as this would be possible with this product because it is touted
as being made for the purpose to ETL. Any help or suggestions are
greatly appreciated.