SQL Server Integration Services is an extremely powerful ETL tool. But if you've done at least a small amount of SSIS development, you've probably encountered some of the head aches and frustrations that come with the territory. This is where BI xPress comes in. In this article, I'm going to cover what I believe to be are the top five reasons you should be using BI xPress.
Reason 5: Expression Management
BI xPress makes it very easy to manage your expressions. The Expression Highlighter allows you to quickly see which objects are being effected by an expression:
The Expression List also allows you view expressions across the entire package and edit those expressions:
The BI xPress Expression Editor also gives you a handy multi-lined window to build your expression. No more one, single, tiny line to build your complex expressions:
Reason 4: Package Deployment
If you've ever had to deploy SSIS packages without using BI xPress, you are not alone in your frustration. You probably created a single project that you move packages in and out of as the need to deploy those packages arises. But with the BI xPress Deployment feature, you can simply open the deployment wizard, select the server, file system, or package store you wish to deploy to, and click the green arrow to deploy 1 package, multiple packages, or the entire project without leaving Visual Studios. Just right-click the package and click Deploy.
The bi-directional deployment feature is also very cool, allowing you to pull packages down from the server to your local machine for editting. The Deployment Wizard also allows you to deploy package configurations and checkpoints to a location you specify.
Reason 3: Auditing Framework
The BI xPress Auditing Framework is invaluable. The Auditing Framework Wizard automatically injects all the necessary event handlers to track package executions, warning, and errors and record them in a SQL Server database. The Auditing Framework can be applied to multiple packages or just one package. The predefined reports that come with BI xPress allow developers to trouble shoot a package without ever opening BIDS.
Want to watch your packages execute on the server and see exactly what step/task it failed on? The new Control Flow Diagram allows you to see a diagram of the package as it executes in REAL TIME on the server without actually opening Visual Studios. You can see row counts, runtimes for each task, and any errors or warnings that may have occured.
This is just one of the reports built into the Auditing Framework. Some of the other reports include the SSIS Execution Dashboard report, Errors/Warnings report, and Extract/Load Trend report.
Reason 2: Notification Framework
The Notification Framework is another very useful tool that BI xPress offers. Using the Notification framework, I can choose to be notified by email or text message of any errors or warning that may have occured during execution. Using the Notification Wizard, I can easily apply the Notification Framework to multiple packages in minutes.
Reason 1: Package Builder
The BI xPress Package Builder feature is probably the single most valuable piece of BI xPress, in my opinion. Package Builder allows you to develop packages at an insane pace. For example, in a recent data warehousing project I was a part of, I was responsible for the ETL. The ETL was estimated to take 3 weeks with 2 people working on it. Using Package Builder, I completed the ETL alone in 1 week. How did I do it? I created a package that contained all of the connection managers, all of the variables, all of the package configurations, and any SSIS tasks that were needed in each package. I then saved the package as a template using Package Builder. When I needed to create a new package, I simply began development using the template.
In the image below, you can see all of the options Package Builder gives you. You can create a template based on some tasks you have selected in BIDS or an entire package. You can then create new packages from those templates.
One of the other very cool things I was able to do with Package Builder in relation to the project I previously mentioned was to apply package templates to existing packages. The client I was working for wanted to apply package configurations to dozens of their already existing packages. To do this, I simply created a template that contained the existing package configurations and applied that template to the right packages. What could have taken days literally took minutes. And now that I've shared my template with the other developers at the client, even the developers there who aren't as familiar with SSIS can correctly apply the package configurations to their packages.
And did I mention that BI xPress does all of this using native SSIS components? Anything you develop using BI xPress will run on any other machine with SSIS installed. This means you don't have to get approval to install a 3rd party tool on your production server. It's ALL native SSIS!
Although I work for Pragmatic Works, this is not a shameless plug, if you can believe that. I don't get paid when we sell BI xPress, so I stand to gain nothing from you buying or not buying BI xPress. With that said, the simple purpose of this article is to enlighten the Business Intelligence community on some ways that their members could drastically improve their SSIS development time. I've witnessed BI xPress single handedly shave weeks off of a project. Regardless of the developer of the product, BI xPress is a very valuable tool that you need in the hands of your developers.
To download a free trial, check out this link: http://www.pragmaticworks.com/Products/Business-Intelligence/BIxPress/