posted 11/21/2011 by DustinRyan - Views: [780]
Last week it was announced that Pragmatic Works' tool, BI xPress, won the SQL Server Magazine Gold Editors Choice Award for Best Business Intelligence and Reporting Tool and the Silver Community Choice Award.
BI xPress allows for real time monitoring of all your SSIS packages executing on your servers. The way that BI xPress can allow you to monitor your SSIS package in real time is by applying a standardize auditing framework across all of your SSIS package using the BI xPress Auditing Framework Wizard. The Auditing Framework captures errors, warnings, and runtime details to create an execution log that can viewed in real time. This information makes it very easy to debug and track down problems with your SSIS packages.
So lets walk through applying the Auditing Framework to several packages a coworker developed to load a data warehouse.
To apply the Auditing Framework, I will select my packages in the Solution Explorer, right-click one of the packages and select Add/Remove Auditing Framework (BI xPress).
After selecting the packages, we need to set up the auditing database where all our package's execution information will be stored. I can also choose to generate an configuration file for the connection to the auditing database.
On the next tab we select our logging options. We can enable real time monitoring, variable change tracking, row counts, connection information, and even SQL statements.
If we have any user variables we wish to track, we can specify those on the Advanced User Defined Logging tab.
After clicking Next we then applying the Auditing Framework.
With the Auditing Framework applied, we can see the Row Count Transforms that are added to our Data Flow Tasks to track the rows from sources and into destinations.
Scripts tasks have also been added to Event Handlers to write to our Auditing Database when the packages execute or if an error or warning occurs.
With the Auditing Framework applied, we can now watch our packages execute in real time on the server.
As powerful as the Auditing Frame is, it's only one of the features of BI xPress. Head over to PragmaticWorks.com to read more about the other features of BI xPress and download the trial version.