Posted by: Colin Smith
Powershell, SQL Server
I have been working on a task that will populate a database with a ton of information about jobs from all my sql servers and allow me to do reporting on it. Of course I chose powershell to write the script to go get all my data and to populate my tables. I am also using some SPROCS in order to validate my data and get the correct data into the actual tables. I am doing that so that Powershell can just dump the raw data into staging tables and then the SPROCS determine what needs to be inserted, updated, or marked as deleted. Notice I say marked as deleted and not deleted. That is so that I will have some history about when the job was deleted from the SQL Server. I will be posting this here as I go along. My database structure is built so that not just sql server job data can be stored but oracle as well. I am not writing the sripts to gather the job info from the oracle instances since those may be in Oracle and may be in Cron. So over the next few weeks you will start seeing DDL, SPROCS, and Powershell code to get this going. Then perhaps some reports as well.