We have several sites (countries if you may call them) and each site has its own set of ERP databases (Progress database). We have designed ETL layers that are open and which can be invoked for each of these sites. On top of the ETL layers, we scheduled
the SSIS packages via SQL jobs for each site and scheduled them to run at various times of the day based on the site operating hours. I want to now present the amount of time it takes to copy data from the ERP system into our SQL 2008 Staging and finally
into our DataWarehouse. I can manually right click on the SQL jobs and get the timings, however, I want to automate this report so that we can look at it initially weekly and maybe monthly when things start to run smoothly. Any pointers or ideas? I know I
can run SQL Profiler and capture the SQL behind this report, would it actually work?
View Complete Post