Tag Archives: audit SSIS packages

Import Native SSIS Performance Data With BI xPress

imageOne of the most powerful features of BI xPress is the Auditing Framework, which allows you to apply a standardized and robust auditing framework to multiple packages in just a few clicks. I’ve blogged about the BI xPress Auditing Framework before because its an extremely impressive tool that has saved me and the teams I’ve worked with countless hours. But now that SQL Server 2012 has been released, we have a whole slew of execution and performance data available to us natively within the 2012 SSIS Catalog. Wouldn’t it be great if we could view the native execution and performance data within the BI xPress Monitoring Console even if the BI xPress Auditing Framework has not been applied?

Your prayers have been answered. If you’re running SSIS 2012, you can now easily import the native execution and performance data into the BI xPress database for even more in depth reporting on the execution of your SSIS packages. Of course, this feature only works if you’re running 2012 :).

To import the SSIS Catalog data into the BI xPress database, click the SSIS Catalog Import button located on the Pragmatic Workbench home screen.

image

Next specify the location of the BI xPress database that is your target for the import. Then add the SSIS Catalog as the source for the import.

image

imageAnd the really nice part is that you can turn on Auto Importing so that the native performance and execution data will be automatically imported into the BI xPress database!

Now that the data has been imported into the BI xPress database, any native SSIS 2012 package stored in the SSIS Catalog can now monitored and measured in the BI xPress Monitoring Console. Head over to PragmaticWorks.com for more information and a trial download.

BI xPress wins “Best Business Intelligence and Reporting Tool” from SQL Server Magazine

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).

1 Select Packages

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.

3 Connection Information

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.

4 Logging Options

If we have any user variables we wish to track, we can specify those on the Advanced User Defined Logging tab.

5 Advanced User Defined Logging

After clicking Next we then applying the Auditing Framework.

7 Processing Summary

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.

8 Row Counts

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.

9 Event Handler Script Tasks

With the Auditing Framework applied, we can now watch our packages execute in real time on the server.

10 Real Time Monitoring

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.

Audit and Watch SSIS Packages Execute on the Server

Among the many cool features of BI xPress is the extensive and robust Auditing Framework available right out of the box. With the BI xPress Auditing Framework, a user can track errors, warnings, row counts, variable, connections, source queries, and many other package properties. Whether you’re applying this incredible Auditing Framework to a single package, a dozen packages, or 100 packages, stepping through the wizard only takes a few moments. In this post, I’m going to walk you through applying the Auditing Framework to an SSIS package.

We’ve got a simple package that loads a dimension in a small data warehouse based on the Cash For Clunkers data.

Capture1

Now lets add the BI xPress Auditing Framework. To apply the Auditing Framework, I can simple right-click the package in the Solution Explorer and select Add/Remove Auditing Framework (BI xPress).

Capture2

This will launch the Auditing Framework Wizard. After clicking Next on the opening screen of the wizard, select the option to apply the Auditing Framework. Likewise, you can remove the Auditing Framework by selecting the other option. Click Next.

Capture3

Select which packages you’d like to apply the Auditing Framework to. You can select packages in a File System, on a SQL Server, or in an SSIS project to name a few locations.

Capture4

Click Next. Now you’ll have to create the Auditing database or select the Auditing database if it already exists. If you need to create the database, specify the server you want to create the database on and click the Create New Database button.  Make sure you click the Create new connection button to create the Connection Manager in the SSIS Package that points to the Auditing database. You can also check the check box to create an XML Package Configuration file.

Capture5

Then click the Logging Options tab. On this tab, you can specify DataFlow Logging, Variable, Connection, and other logging properties. Using these options, we can specify to log dataflow source and destinations row counts, real time data flow monitoring, connections, variables, and other properties. All of these options are selected by default so we’ll leave these selected.

Capture6

On the Advanced tab, you can specify if there are any other customer user variables that you would like to log. And on the Alert Filtering tab, you can specify which warnings you do not want to log, for example any truncation warnings using the Warning Editor.

Capture7

Click Next. Then click Start to apply the Auditing Framework to the package. When the process is complete, click Finish. Now that we’ve successfully added the Auditing Framework to the package, lets take a look at it.

The first thing you’ll probably notice is the new Connection Manager connecting to our Auditing database where all our auditing data is kept. Then if you open any data flow tasks, you’ll notice that they appear “jumbled up” after adding the Row Count Transforms. Simply reformat the data flow. Notice the Row Count counting rows out of the source and the second Row Count transform counting rows into the destination. The row count info is stored in variable.

Capture8

This information is then written to our database using tasks located in our SSIS package’s Event Handlers, such as OnError, OnWarning, OnPreExecute, OnPostExecute, and possibly in the OnVariableValueChanged Event Handler if you specified to track variable change history.

Now that we’ve applied the Auditing Framework, I’ve deployed the package to my server and executed it a few times in order to create a history of executions. One of the really cool things about BI xPress is that after applying the Auditing Frameworks we can watch our SSIS packages execute on the server as if we were watching them in BIDS! To open the BI xPress Monitoring Console, click the BI xPress menu in BIDS and select SSIS Monitoring Console.

Capture10

In the SSIS Monitoring Console, one of many reports you can use to troubleshoot your SSIS package executions is the Execution Control Flow Diagram. We can watch the Control Flow and Data Flow while the package executes on the server. Any Errors and Warnings that occur will be instantly visible and we can also see the values of logged package variables. To see a full list of all the reports available right out of the box, click this link.

The BI xPress Auditing Framework is unlike any other out of the box framework available. It’s complete and thorough and gives us, as SSIS developers, more than Microsoft ever dreamed in regards to SSIS troubleshooting. To download a free trial of BI xPress, visit PragmaticWorks.com.