Rather than talk about SQL Audit or setting up your own auditing, I want to talk about some awesome new auditing that happens automatically in SQL Server 2012 that you may not even know about. As a DBA, I don’t know a great deal about SSIS and creating fancy SSIS packages, but I am quite often called upon to troubleshoot why a package fails in production. And quite regularly, the package does very little, if any, logging of what it does. If the error is not obvious in the output of the SQL job that runs the package, it can be difficult to find what happens. That was my life as a DBA before SQL Server 2012.
Just this morning I was checking on why an SSIS package had failed that morning. This particular package is one that I wrote myself, so I know for a fact that there isn’t any fancy logging in it. At first look, the output of the SQL job doesn’t really tell me much about why it failed.The key part of the output message was:
Package execution on IS Server failed. Execution ID: 18717, Execution Status:4.
To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report
No reason why it failed, but it did tell me that there is an All Executions report that I can use. To see the report, I actually had to expand the Integration Services Catalog and right-click on the SSISDB catalog. From there, I navigated in the popup to Reports -> Standard Reports -> All Executions. When the report loaded, it showed a listing of all recent job executions. I scrolled through the list (it auto-loads the next page of results if you scroll past the end of the page) until I found my failure. There were a number of links associated with the failure: Overview, Messages, and Performance. I clicked on Overview and got a breakdown of the execution of the package as a whole and each individual step inside of the package.
The report showed me exactly which step in the package failed. I drilled down into that step that failed. This gave me a breakdown of every phase of execution of that step including a detailed error message where it had failed.
Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Transaction (Process ID 87) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”.
Summary
Thanks to the internal auditing that SQL Server 2012 is doing of my SSIS packages and the reports that put the information right out there for me to find so readily, I was able to determine the cause of the failure in a fraction of the time I would normally spend. Very helpful feature.
As an administrative side-note, if I right-click on the SSIS catalog and click Properties, I get access to the settings for the internal package auditing. I can see how many records are in there and how much space it is taking up. I can edit the retention of the logs, enable/disable clean-up for the logs, and change the level of logging. all of the information I found above is just with the Basic logging level.
T-SQL Tuesday #45 Summary–Follow the Yellow Brick Road | Mickey's T-SQL Ponderings
[…] Robert L Davis wrote about a type of auditing that I hadn’t even considered, and I’m happy he did. He wrote about a new feature in SQL Server 2012. SQL Server 2012 will automatically audit the execution of your SSIS package. He goes on to share how he was able to use the feature. […]
Chris Yates
Spot on; nicely done. I’ve utilized this method myself. Thanks for taking the time to share it.
SQLSoldier
Thanks Chris! I was excited to discover it. 🙂