At a former employer I had put together a series of packages that would use an instance inventory database to connect to multiple instances and colelct information for that inventory from the servers themselves, such as user, disk, configuratioon information.  BrentO and I happened to share the same job for a bit and tweeted about it, and a few people (bmbowman and cfrandall) expressed interest in seeing the code.  Since the collector was always a side project to my DBA duties, I wasn’t so great about setting up proper deployment scripts (bad DBA) but I’ve tried to put them together in this archive and share with whoever.

If there are any errors let me know and I’ll try to fix them, but be gentle.  The only testing I have done so far is to try and scrub out any company specific username/password and set up a re-runnable install script for the SQL objects.  There is a README.txt file included in the archive with all the relevant information I could this of at the time but I’m sure I have forgotten something.  NOTE: SSIS 2008 changes the logging table from dbo.sysdtslog90 to dbo.sysssislog which makes a lot more sense, except it only shows up in the “System Tables” container.  Grrr.

Here is the archive containing the SQL, DTSX and DTSXConfig files.

Controller.dtsx is a SQL 2008 SSIS pacakge

Files to edit:

• Config.dtsConfig (Server Name, Database Name)
• SetupScripts.sql (Encryption passwords, Credential insert constants)
• Controller.bat (use full path to files)
• Tasks\PushDBMail.bat (use full path to files)
• Tasks\PushDBMail.dtsConfig (change email address to send errors to)
• MetricPackages\*.dtsx (These are SQL 2005 packages and may need to be upgraded)