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.)

The Great Pragmatic Works Task Factory Round Up

Over the last month or so, I’ve been blogging about my companies (Pragmatic Works) cool suite of SSIS (SQL Server Integration Services) add in tools called Task Factory. I did this mostly to learn about the various components, and decided to video the whole thing in order to a) help myself remember and b) share the knowledge with everyone.

I’m about to move onto some of our other tools, and as I explore and learn them will be adding videos as I go. I wanted to wrap up the Task Factory series with a summary of all the tools, and links to the blog post and the direct link to the video. Please note I’ve listed these alphabetically, to make them easy to find, instead of the order in which they were published. 

Task Factory Address Parse video | blog

Task Factory Advanced E-Mail and SMS Task video | blog

Task Factory Case Transform video | blog

Task Factory Compression video | blog

Task Factory Data Cleansing video | blog

Task Factory Data Validation video | blog

Task Factory Delete Batch Transform video | blog

Task Factory Dimension Merge Slowly Changing Dimension video | blog

Task Factory Email Source video | blog

Task Factory File Properties video | blog

Task Factory Null Handler blog

Task Factory Replace Unwanted Characters video | blog

Task Factory RegEx video | blog

Task Factory SalesForce.com Source and Destination blog

Task Factory SharePoint Destination video | blog

Task Factory SharePoint Source video | blog

Task Factory Surrogate Key transform video | blog

Task Factory Terminator Destination blog

Task Factory Trim Plus video | blog

Task Factory Update Batch transform video | blog

Task Factory Upsert Destination video | blog

Task Factory XML Destination video | blog

Task Factory–SalesForce.com Source and Destination

Over the course of the last month I have created video tutorials on the components that ship with Pragmatic Works Task Factory suite of SSIS components. There are two though, that unfortunately I’m not able to cover, but I did want to make sure everyone was aware of. Those are the SalesForce.com Source and Destination components.

These are quite similar to the SharePoint Source and Destination components I covered in previous posts. Here’s a screen shot of the Source properties window:

 

SNAGHTML6a6b6c3

 

As with most of the tools in Task Factory, you simply pick the connection manager, pick the object to connect to, supply and parameters, and pick what you need. Just that simple and straight forward.

The Destination component is just as easy, as you can see:

SNAGHTML6aafcf6

Again, simply create a connection, pick the target object, map the columns and away you go.

Since I don’t currently have a way to communicate with a SalesForce.com environment, as of this writing we’ll have to settle for a few screen shots. I believe though they will convey the simplicity of the product. Most importantly though you are now aware of these handy components, something you’ll find invaluable if you deal with SalesForce.com.

Task Factory–SharePoint Destination

In a previous video we looked at the SharePoint Source component, one of the many components in Pragmatic Works Task Factory suite of SSIS tools. We saw how easy it was to extract data out of a SharePoint list.

What though, do we do if we need to push data into a SharePoint list? It turns out it’s every bit as easy, using Task Factory’s SharePoint Destination component.

Task Factory–SharePoint Source

Microsoft SharePoint has taken the enterprise by storm. A staggering number of corporations today are using SharePoint as their collaborative and information backbone. A tremendous amount of information is being stored within SharePoint lists.

As such, it’s becoming more and more important to be able to quickly and easily extract the data in those lists and bring it into other platforms for analysis, reporting and storage. SQL Server Integration Services seems like the ideal tool for moving and transforming this type of data around, but accessing data in SharePoint lists is not a trivial task.

Unless of course you have Task Factory. Task Factory is a suite of SSIS components from Pragmatic Works. In this video, we’ll look at the SharePoint Source component.

 

Task Factory Dimension Merge Slowly Changing Dimension

For any ETL developer, updating dimensional data is the heart of what you do. Using out of the box SSIS components, however, is an unattractive proposition. You either had to use the built in SCD wizard, or use the "roll your own" approach. As any veteran BI developer knows, the SCD wizard isn’t the best in the world, primarily due to it’s reliance on the OLEDB Command task. "Roll your own", in other words handling all the logic yourself, works, but is time consuming to develop and often confusing to maintain.

A far, far better solution for handling Kimball SCD is to use the Task Factory SSIS Dimension Merge Slowly Changing Dimension transform. While it’s name is rather long winded, it’s definitely worth the breath.

In this video we’ll take a look at the Dimension Merge Slowly Changing Dimension in action.

Task Factory Update Batch Transform

Task Factory is a suite of SSIS components available from Pragmatic Works. In this video we’ll look at the Update Batch Transform.

Updating data can be a real pain. You either have to setup a special staging table in your database, then update from it, or use the slow OLEDB command. In this video we’ll look at a better solution, Task Factory’s SSIS Update Batch transfrom.

Follow

Get every new post delivered to your Inbox.

Join 106 other followers