Category Archives: Uncategorized

The Tool Any Serious SSIS Developer Shouldn’t Be Without

If you’re not familiar with the BIDS plugin by Pragmatic Works called BI xPress, you really should check out this tool. For the small amount of money you’ll spend on BI xPress, you’ll add a ton of powerful weapons in your SSIS/SSAS development arsenal that no serious developer should be without. Since I’ve been working for Pragmatic Works for the past couple years I’ve had many opportunities to work with BI xPress. I can definitively say without a doubt that the gigs I’ve been on where I have BI xPress as a development tool go much much faster simply because of the additional weapons in my SSIS development arsenal. I’ve blogged this before, but I just wanted to take a couple paragraphs to talk about my all time favorite BI xPress feature, as well as the newest BI xPress feature.

My number one favorite feature of BI xPress is the Package Builder Wizard. In my opinion, this is probably the most powerful development tool in BI xPress. The reason the BI xPress Package Builder Wizard is so powerful is simply because it allows you to speed up your package development.

Capture

Using the Package Builder Wizard, a developer/development team can develop and manage package templates. So imagine you have a couple senior SSIS developers on your team and you also have a couple junior SSIS developers on your team. The senior SSIS developer can develop package templates with the more complex pieces of the package already developed and then pass those on to the junior guys. I’ve also seen the senior team members develop package templates with the correct naming conventions, necessary variables and connections, and annotations. These templates are then used as a starting point for the main development team, which saves the developers a great deal of time.

One of the great things about the Package Builder Wizard (its also my personal favorite feature) is the ability to alter existing packages with a prebuilt template. I once did some work for a client that needed to add a connection manager to an auditing database, event handler logic, and package configurations to 100 or so packages. Without BI xPress, this would have taken a long time. I don’t even want to imagine how long it would have taken. But without BI xPress, this only took me less than an hour (not counting testing 😉 ). I developed a template which included all the necessary variables, connection manager, event handler tasks, and package configurations. I then applied that single template to all of the packages in my project. It was literally that easy. Needless to say the client was pretty excited that it only took a few minutes to get through the work, at which point they then instructed me not to tell anyone I had completed the work that quickly :).

The newest feature of BI xPress is the MDX Calculation Builder. In short, the MDX Calculation Builder allows you to quickly build MDX calculations without having to script them out by hand. Do you need to script in YTD, QTD, MTD calculations? The BI xPress Calculation Builder can do it in 3 steps. Do you need to calculate the percent of a parent, compare a measure to a parallel period, or calculate an average? The calculation builder can do all that, too, all without manual scripting of MDX. It can even build dynamic sets for you.

Capture2

There are tons of other feature in BI xPress that I haven’t even mentioned that make BI xPress really just an insane tool no serious SSIS developer should be without. And since it uses all native SSIS components, the only place BI xPress needs to be installed is on the machine where development takes place. If you haven’t checked out BI xPress, I highly suggest you download the free trial and just give it a shot.

Greetings, SQL World!

As my first post on my new blog site, I figured I’d give a bit of an introduction. I’m a business intelligence consultant with the Pragmatic Works crew in Jacksonville, Florida. Lately I’ve been creating BI solutions utilizing SSRS, SSIS, SSAS, and SharePoint. My experience stretches through a variety of industries such as health care, manufacturing, transportation, retail, and insurance, just to name a few.

My primary purpose for this blog is to discuss SQL and BI related topics and to interact with the professional SQL community. I look forward to making new friends, learning lots of new things, and sharing my experiences!

You can find me writing here on my blog, consulting with any of the many clients of Pragmatic Works, or tweeting @SQLDusty.