When working on developing a Datawarehouse, there are going to be ETL operations which is going to involve lot of SSIS packages and related objects. One of the key things to keep in mind while building SSIS solutions to make sure there is sufficient auditing of the packages, which would also enable smooth troubleshooting of issues with SSIS packages. In my earlier blog posts I have talked about Logging in SSIS, let us say for example the scale of ETL has increased meaning it spans different servers. Brian Knight founder of Pragmatic Works has come up with Best Practices around monitoring SSIS packages.
Here are the Basic Principles.
Rule #1: Defragment Your Auditing
The better alternative is to create a centralized database to receive all the events from all of your SSIS servers. Then, create a series of reports on top of that centralized database.
Rule #2: Use Event Handlers and Row Counts for Auditing
You will also want to trap Warnings, PostExecute and PreExecute events in the event handler tab. Doing so will allow you to see when a step starts and stops.
Rule #3: Success does not mean Success
Folks who have been developing SSIS solutions can talk to Rule#3. There are situations where data rows would be transferred from the source but rows did not go through to the destination. A rowcount check needs to be performed on the source and destination. In case the row counts do not match send a email/raise a alert, this could avoid costly manual troubleshooting time.