SQL Server and its related business intelligence (BI) tools are comprehensive and can be challenging to master. One of the BI tools, SQL Server Integration Services (SSIS), lets you create pieces of functionality called packages that can move data, work with the file system, send email, and perform many different types of database maintenance tasks. SSIS also provides a Script task that lets you code custom functionality into a package using the .NET language of C# or Visual Basic .NET. However, configuring, deploying, and maintaining SSIS packages can be difficult and time consuming. Pragmatic Works Software’s BI xPress (Standard Edition) provides many features that can help you develop and deploy SSIS packages more quickly, write expressions, and more.
BI xPress requires SQL Server 2005 or later with Business Intelligence Development Studio (BIDS) and SSIS installed. The supported OSs are Windows 2000 Server and later and Windows XP and later.
I installed BI xPress on a Windows 7 machine in a matter of minutes. All that was required was the download of a .msi file from the Pragmatic Works website. After executing that file, I was presented with a wizard that walked me through the rest of the process.
Once installed, you can access BI xPress’s UI, which Figure 1 shows, two ways. You can open it by clicking the BI xPress icon on your desktop. Alternatively, you can access its functionality from within BIDS through the BI xPress menu item or through the right-click context menu. Having both access points is helpful because you might not be using BIDS when you want to use BI xPress.
Figure 1: BI xPress’s UI
BI xPress offers a lot of functionality, all of which revolves around assistance with SSIS package development, deployment, debugging, and monitoring. The development features include the Package Builder Wizard, Snippet Wizard, and Expression Manager. The Package Builder Wizard lets you build new packages from more than 100 pre-existing templates (with more to come in future releases), modify an existing package or template, and create your own templates. The templates included with this software represent typical tasks needed by SSIS developers and are divided between SQL Server 2008 and SQL Server 2005. The functionality provided by these templates includes the ability to extract user information from Active Directory (AD), data cleansing, and much more.
The Snippet Wizard adds Script tasks and related code to your existing SSIS packages. It currently provides 29 different code snippets, including code to generate formatted HTML from a SQL query, send an HTML email, and zip and unzip files. I would like to see more code snippets, because they’re a big help to developers who are a little weak in C# or Visual Basic .NET. The snippets not only speed up your development time but also provide good examples from which to learn.
The ability to use expressions in packages makes them flexible, but the functions available in SSIS don’t always work as expected. A simple example is the DatePart function. In SSIS, you need to put double quotes around the datepart argument (the first option in the function), but this isn’t done in the T-SQL version of the function. This lack of consistency makes developing expressions a little slower than it has to be. Expression Manager really speeds up this process by storing a comprehensive list of expressions for your use. It also lets you store and organize the expressions you write.
With BI xPress, you don’t just get help with developing packages. You also get help configuring and deploying them. When you use the SSIS Secure Configuration Wizard, you can create encrypted configurations as well as edit existing configuration files, saving time and effort. The Package Deployment Wizard makes deploying packages faster and easier. It lets you change the package protection level, deploy configuration files, and set the location of the checkpoint file while deploying your packages.
Once deployed, you can use the Auditing Framework Wizard and SSIS Monitoring Console to debug and monitor your packages. The Auditing Framework Wizard lets you inject an auditing framework into any package. With this framework in place, you can log to a central database, control which events get logged, and enable row count logging. The auditing framework also gives you the ability to use the SSIS Monitoring Console. You can use this console to monitor packages in real time, replay a package run, and obtain SQL Server Reporting Services (SSRS) reports that show the efficiency of your packages, making it much easier to find and tune the poor performers. With BI xPress’s monitoring console, you can watch more packages simultaneously during run time and get more package information compared with what Microsoft offers in SSIS.
If you need to provide notifications when certain events occur during deployment, you can insert a notification framework into your packages with the Notification Framework Wizard. You can receive notifications about package failures via email (HTML or text), Short Message Service (SMS) text message, Windows event log, or log file. The notification framework relies on a configuration file to track who will be notified. The highly customizable notifications provide a more readable version of the error message, as well as a lot of other package- and session-related data. The notification framework doesn’t require any server-side components, which is very helpful if you’re not allowed to install software on the server.
I only brushed the surface of the functionality that BI xPress provides. On the Pragmatic Works website, you can find many videos that provide an overview of the features. You can also download the free Community Edition, which does a good job of introducing you to the software.
I like BI xPress and believe that it can prove very useful to anyone who develops SSIS packages. It’s extremely comprehensive and seems to offer more with each release.
|
BI xPress |
