Question: When we run SSIS in development (from SSDT/BIDS), we can see the progress of the job, with all of the steps highlighted in yellow, red or green. Is there a way to display this same status information when the packages run in production?
You have a number of options when it comes to monitoring SSIS packages at runtime. In 2005/2008, you needed to create your own solution (based on SSIS logging, or custom logging approaches). In 2012, you now have built in reporting and monitoring from the SSIS Catalog (although the previous methods still work as well). There are a number of third party and open source logging frameworks and monitoring solutions, and of course you can always roll your own using the SSIS APIs.
A quick search turned up a number of options (some of which I wasn’t previously aware of):
SSIS Catalog (2012)
- Packages run through the SSIS Catalog can be monitored through the default reports / views
- You can also build your own reports based on the catalog views
All versions
- BI xPress has an auditing framework and SSIS monitoring console that gives you a BIDS like view at runtime
- Enabling logging on your packages and use the sample reports
- Use a custom logging framework (1, 2, 3, 4, 5, 6)
- Run the packages with a custom application and capture events at runtime (similar to DTLoggedExec)
- View execution logs using BI System Monitor and the SSIS Log Analyzer
Thanks for the solid research of monitoring solutions. referenced this solid list in my Simple SSIS logging article. http://troywitthoeft.com/simple-ssis-package-moni…
Unfortunately, all of the options above involve editing your existing packages to include additional code or worse convincing your development team to adopt a new framework. This could take time and depending on your solution substantial cost.