Over the last couple of weeks I’ve set up a SSIS package that will loop through a group of servers and export the list of databases to a central location. I used a Foreach Item Enumerator to list the servers but honestly those require a bit too much maintenance for me. In my office we have 60-70 instances right now and are merging with a group with 60+ more. In my mind that’s a headache waiting to happen. So today I’m going to modify the package to pull and loop through a list of servers from a table. Still somewhat manual (entering records into the table) but at least I don’t have to open the package each time.
CREATE TABLE InstanceList ( InstanceName varchar(255) NOT NULL CONSTRAINT pk_InstanceList PRIMARY KEY, GetDatabaseList bit NOT NULL, Active bit NOT NULL )
SELECT InstanceName FROM InstanceList WHERE Active = 1 AND GetDatabaseList = 1
At this point we now have a package that will loop through a control table containing a list of instances. Then for each instance it pulls a list of databases and stores it into a central location. Hopefully I’ve managed to make these posts clear enough that everyone can follow and easily duplicate what I’ve done. If not then please feel free to comment and I’ll do my best to help you get up and running.