Tag Archives: Pragmatic Works; Task Factory; SSIS; SQL Server; SQL Server Integraion Services

Creating a Data Warehouse Date Id in Task Factory Advanced Derived Column Transformation

The company I work for, Pragmatic Works, makes a great tool called Task Factory. It’s a set of transformations that plug into SQL Server Integration Services and provides a wealth of new controls you can use in your packages. One of these is the Advanced Derived Column Transformation. If you are familiar with the regular Derived Column transformation built into SSIS, you know that it can be painful to use if you have to create anything other than a very basic calculation. Every try typing something complex into that single row tiny little box? Egad.

The Task Factory Advanced Derived Column transform allows you to pop up a dialog and have true multi-line editing. In addition there are 180 addition functions to make your life easier. Which is actually the point of this whole post.

As a Business Intelligence developer, one of the things I have to do almost daily is convert a date data type to an integer. Most dates (at least in the US) are in Month / Day / Year format. Overseas the format is usually Day / Month / Year (which to me makes more sense). SQL Server Analysis Services loves integer based field, so a common practice is to store dates as an integer in YYYYMMDD format.

Converting a date to an integer using the derived column transform can be ugly. Here’s an example of a fairly common (although not the only) way to do it:


(DT_I4)((DT_WSTR,4)YEAR(MyDateColumn) + RIGHT("00" + (DT_WSTR,2)MONTH(MyDateColumn),2) + RIGHT("00" + (DT_WSTR,2)DAY(MyDateColumn),2))

Task Factory makes this much easier. There is a ToChar function which converts columns or values to characters. This function allows you to pass in a format to convert to. Wrap all that in a ToInteger function and away you go. Check this out:


ToInteger(ToChar(MyDateColumn, "yyyyMMdd"))

Much, much simpler. One thing, the case of the format is very important. It must be yyyyMMdd, otherwise it won’t work. If you want to extend this more, you can actually check for a null, and if it is null return a –1 (a common Id for a missing row) or another special integer to indicate a missing value, such as 19000101.


IIf(IsNull(MyDateColumn)
   , -1
   , ToInteger(ToChar(MyDateColumn, "yyyyMMdd"))
   )

Here we first check to see if the column is null, if so we return the missing value, else we return the date converted integer. And yes, you can do multi line code inside the Advanced Derived Column Transformation.

As you can see the Advanced Derived Column Transformation makes working with dates much, much easier than the standard derived column transformation. This is such a common need that, at the risk of sounding like an ad, I decided to blog about it so I can share this with all my clients in the future.

(Just to be clear, it’s not an ad, I was not asked to do this, nor did I receive any money for it. Mostly I did this post just so I could share the syntax when I start each project or training class.)

Task Factory Advanced E-Mail and SMS Task

I’ve been continuing my learning process of my companies Task Factory suite. Building on my previous work with the Compression task (video) and File Properties task (video) I’ve now created a new video about the Advanced E-Mail and SMS Task. After compressing a file, e-mailing it out seemed like a natural thing to do, so this falls in nicely with the previous videos.

 

Task Factory Terminator Destination

I’ve been exploring some of the many controls included with Task Factory since I came to work for Pragmatic Works. One that I think is useful but underappreciated is the simple Terminator Destination. This is an extremely handy tool to use during development. Let’s take a look at a common scenario.

terminator1base

As you can see from the above screen shot, we’re in the process of developing a package. We’ve worked out the logic in the left branch, and are now working on the right. We have a derived column transformation called “Do some more complicated stuff”. It’s got some complex logic, and we’d like to look at the output before we write it to our target destination. That means having a destination after the derived column transform so we can put in a data viewer. So what are our options?

One of the most traditional is the row count, as seen below.

terminator2rowcount

While this seems pretty simple, it required a few setup steps. First, we had to create a temporary variable to hold the results of the row count. Then we had to open up the row count and assign the variable to the row count transformation. Sure, this seems simple, and I suppose it is. But it does take a few minutes to setup, and I can’t tell you how many times I’ve opened up a package to find variables that aren’t being used for anything. Most times these were temporary row count variables that never got removed. That meant a lot of time tracking down what those were used for, and extra testing to ensure there removal doesn’t cause issues. Suddenly those few minutes add up.

An alternate solution might be the script task. With the script task, there’s no variables to be created.

terminator3script

It looks pretty straightforward, but before you can use the script task you have to open the task, then create a script, then save it, and finally close the component. Again not a huge time investment, but very annoying to have to go through all those steps just to have a temporary destination.

A better, and simpler alternative is the Terminator destination that comes with Task Factory.

terminator4terminator

To use the Terminator, you simply drop it on the design surface, then hook it to the derived column transform above it. Add the data viewer and your done. No variables, no configuring anything, you don’t even have to open the control to edit anything on it.

I’m surprised at just how nice development has been using this tool. Dropping the destination on the package is a “thought free” task. I don’t get distracted, having to stop and think to create a variable, or remember to open and edit a script. Since I don’t have to think about it, it means my concentration remains where it needs to be, on the calculations I’ve created in the derived column transform.

Start using this little transform during your development, I think you’ll find it to be one of the most pleasant transforms you’ll ever use.

Disclaimer: Although I said it in the initial paragraph, just to be clear I do work as a consultant for Pragmatic Works, and Task Factory is one of their products.