I’ve been using Pragmatic Works Task Factory to prototype a Business Solution (BI) for a new client and want take a minute to list my likes and dislikes. First, I’m no expert as I’ve only used it a month but their video tutorials have helped get started. Some of the individual components, such as Slowly Changing Dimension (SCD) component, have older versions available as freeware through Codeplex. The benefit of using Pragmatic Works is having supported compenent versions to keep up with your SQL version.
Time vs. Money
So as a BI consultant, I have to look at tools from the point of view of time vs. money. Is the money I spend on a tool going to save me and my clients enough development time to justify the expense? Definite yes on this because the tools have been written by SSIS users and developers. They needed better tools to do repetitive tasks so they wrote it.
Is there going to be a steep learning curve that wastes other time? Not at all. Every tool that I’ve used has its own help article and video tutorial to get you started. Pragmatic Works does a great job of helping new users hit the ground running.
Favorite Control Flow Tasks:
– Advanced Execute Package Task. This adds the ability to specifically map variables between the parent package and the child. Just a little bit more control for those of us that like to control the details.
– Compression. I haven’t used this yet but it would have been handy on some past projects where we had to unzip, process and re-zip flat files.
– Secure FTP. Again, I haven’t used this yet but it would have saved a lot of scripting and workarounds on past projects.
Favorite Data Flow Components:
When you try or buy Task Factory, you are getting over 50 SSIS components. I can’t review them all, so a few favorites so far:
– NULL Handler, an easy way to handle nulls.
– Replace Unwanted Characters, for cleaning up those messy !@#\| characters that seem to creep into text columns during file loads or just cleaning up data.
– Upsert Destination – Great for fast loading of large dimensions. This was on Codeplex for years, but the last update there was 2011. If you want a version for SQL 2012 and 2014, then the Pragmatic Works version is the way to go. This is easy to configure for inserts and updates based on the keys you choose. It can also handle updates in several ways including doing a column compare, timestamp compare or a last update compare based on a last update column.
– Dimension Merge SCD – If you grok SCD’s, then you need this component. This component needs a blog post of its own.
Again, I am still learning all the components in the tool, but if you are working with SSIS and need more than the basic toolbox this is a great addition for a developer. I’ll write more later and if you attend my sessions at SQL Saturday, you’re probably going to hear about some of these.