Adding robust auditing to your SQL Server Integration Services packages can be a lot of work if you are forced to create it yourself. The Pragmatic Works BI xPress software enables you add auditing with minimal effort and includes several built in reports that provide detailed information about your ETL processes. As a consultant for Pragmatic Works I love being able to show off this product to clients on a weekly basis.
Frequently, I receive requests for additional product features that I pass along to the development team. Recently, I was working with a client that requested an additional filter for the built in reports. The client asked if there was a way to identify “sets” of Integration Services packages. This client was using the BI xPress auditing tool for more than one ETL solution and thus the auditing reports would display audit records for Solution 1 mixed in with Solution 2. After looking into the problem, we were able to extend the BI xPress report filtering to achieve “package set” based filtering. In this article I will provide you with detailed instructions for adding this to your auditing solution.
Two different solutions “Solution 1” and “Solution 2” are logging to BI xPress. The audit records for each of the solutions are mixed together in the reports. Below I have included a screen shot of the Report Execution Summary report. For sake of this example, each solution’s package names include the name of Solution itself. While this provides a way to visually distinguish the two sets of packages, they are still mixed together in the report.
Add a package variable to each package that contains a description of the set/solution to which it belongs. The screen shot below shows the variable “PackageSetName” that I used to tag each package with its associated set name.
When adding BI xPress auditing, include custom variable logging for the package set variable in the Advanced User Defined Logging tab. Make sure that you tell BI xPress to log on the OnPreExecute (Start) and OnPostExecute (Stop) events.
(This is the tricky part.) BI xPress reporting uses a series of the stored procedures in the auditing database to retrieve the records for each report. The stored procedures accept the report filters that you as a user enter in the report parameters screen. I have included a screen shot of the screen below.
The trick is to alter each of the report stored procedures to take one of the parameters and use it to filter on the logged variable included in Step 2. The best candidate for this is Machine Name since it is used in all report stored procedures. We do not want to lose the ability to filter by Machine name however. To make this parameter dual purpose, the revised stored procedures looks for a tilde (~) within the parameter. The string to the left of the tilde is used for the Machine Name and the string to the right is used for the Package Set filter. I have included a snippet of the stored procedure modifications below so that you can see what is happening.
The same basic change is used in all the report stored procedures so implementing the change was pretty much a cookie cutter operation once I was satisfied with the results. You can use the following link to download the complete set of PROC ALTER scripts to implement this change.
I am a BIG believer in safety nets. SO, BEFORE making any changes to your BIxPress auditing database, please export the original stored procedure definitions so that you can more easily undo these modifications. The following stored procedures were altered to implement the set filtering system.
Demonstration Of Package Set Filter:
Now, by including the Solution-1 package set name to the Machine Name parameter, BI xPress reports will only display those packages associated with Solution 1.
And for Solution 2 …
If you do not include the Package Set filter, the report parameter still functions as it did before.
I hope you find this tweak helpful! It adds some nice functionality to an already awesome tool!