If you are a Regular Expression guru, you’ve probably wished you could use Regular Expressions within your SSIS Packages. With the Task Factory SSIS RegEx Replace Transform, part of the Task Factory suite of SSIS components from Pragmatic Works, you can do just that. Let’s see how…
Of all the components in Task Factory, by far the coolest is the E-Mail Source. Using this nifty component you can actually pull your data into SSIS from an E-Mail! In this video you can see the Pragmatic Works tool in all it’s glory. Then you can see that the Task Factory SSIS E-Mail Source, like bowties, is cool.
When working with data, I often need to delete rows, but only rows for a specific set of keys I’m working with. The Task Factory SSIS Delete Batch Transform, from Pragmatic Works makes this easy. In this video, we’ll make like a barbarian king and hack and slash our way through data with ease.
When extracting data, you don’t always a natural key handy. Sometimes the nature of the data negates any built in key, or perhaps there’s just not one present. For these situations the Task Factory SSIS Surrogate Key Transform, part of the Task Factory suite from Pragmatic Works, will meet your needs.
At it’s core it’s very simple, just add it to your data flow, provide a column name, seed value and increment value, and away you go. It’s almost so simple you don’t need a video, but our series wouldn’t be complete without including this little gem.
In previous videos, we’ve looked at some of the other data cleansing operations Pragmatic Works’ Task Factory offers, such as address parsing and case correction. Another common data cleansing task is trimming spaces or characters from the start and end of text columns. In this video, we’ll do some data cleansing with Task Factory’s Trim Plus transform.
XML has become the defacto standard for information exchange across the web. The Task Factory SSIS XML Destination, one of the many components found in Pragmatic Works’ Task Factory suite, makes it incredibly easy to create XML files from your SQL Server Integration Services packages. In this video, you will see just how simple it is to generate easy to read XML files.
Inserting and updating records is, without a doubt, the most common operation performed in any SQL Server Integration Services package. What a headache though, having to determine if the record exists, whether to insert it, stage it, etc.
All that goes away with the Task Factory SSIS Upsert Destination component, part of the Task Factory suite from Pragmatic Works. With one simple control you can handle both inserts and updates with ease. This video shows you how incredibly easy it is.
The Case Transform, an SSIS component in the Task Factory suite from Pragmatic Works, allows you to easy correct the capitalization mistakes found in many source systems. This video demonstrates how easy it is to use the Task Factory Case Transform SSIS component.
Addresses. They are one of the biggest headaches for any ETL developer. Trying to clean up poorly, inconsistently entered addresses can be a real headache. Fortunately the Address Parse Transform, part of the Pragmatic Works Task Factory SSIS suite, is your aspirin. In this video you will learn just how easy it is to clean up all those bad addresses.
One of the most common tasks for SSIS developers is to replace characters or words in a column with a new set of characters, or a new word. Sure, you can use a derived column transform, then fiddle around with substrings, but geez what a pain that is. Fortunately Task Factory, a suite of SSIS components from Pragmatic Works, has a great tool to make this quick and easy: The Replace Unwanted Characters transformation. This video demonstrates how easy it is to use.
Disclaimer, In case it’s not obvious I do work for Pragmatic Works.
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.
My last video on the Task Factory Compression Task was well received, so I thought I’d build on it with a video on the File Properties Task. It’s a cool little tool that will grab any of the various attributes associated with a file. If you want to see more about Task Factory, you can head on over to my employers website, Pragmatic Works.com
In previous posts I’ve been working through some of the Task Factory components. As many of you know I went to work for Pragmatic Works earlier this year. Since then I’ve been learning all our tools, and since there’s no better way to learn than by teaching am creating blog posts about these tools.
Today I thought I’d look at the TF Compression Task. They say a picture is worth a thousand words, not sure what that converts to in term of video but I feel it must be a lot. So without further delay, here is my video showing how to use the TF Compression Task. You can also find it directly on You Tube at http://bit.ly/tfcompression
Nulls. Seems like they are the universal headache among all data based projects. Trying to clean up nulls in SSIS is certainly doable, but not without some work with expressions. An equal headache to null values are blank values. In most cases, you’ll either want these converted to a null, or some specific value. Because null handling is such a common task Task Factory has a transformation specifically designed for working with nulls, the Task Factory Null Handler.
There are three options for handling nulls / blanks within the Null Handler.
- Convert Null to user defined value
- Convert blank value to null
- Convert blank value to a user defined value
Let’s take a look and see just how it works. First, we’ll need some sample data to play with. Here’s a small script I created to do just that.
It’s only a few rows, enough to test the various modes Null Transform supports. In the first row, we’ll be converting a null date to a defined value, specifically the date 9999-12-31. In the second row, we’ll convert a null text field to a user defined value, "Was Null”. In the third row, we’ll convert blank text to user defined text. In the final row, we’ll convert a blank to a null.
Now that we have our data in place, it’s time to setup the SSIS package. Create a new package and drop a data flow task on it. Start with a standard OLEDB source, and hook it to the database and NullTestSource table.
Next, drag the Task Factory Null Handler on the display surface, and hook it to the source. When you open it, you’ll see a list of the input columns, to the right they will all be set to ‘No Action”. Expand the drop down for the action beside the SomeDate column. Change the action to “Convert NULL value to user defined value”.
Now click the drop down beside the value. In the Parameter Type drop down are three options, Direct Input, Column, and Variable. These are the various choices you have for providing a replacement value. For this exercise, we’ll just use the Direct Input. Change the replacement value to 12/31/9999.
Repeat the steps for SomeText1, only for it’s replacement value use “Was Null”. For SomeText2, select “Convert blank to null” for the action. No other configuration is required for this action. Just leave the SomeAction column set to “No Action”. It’s merely present to tell us what action the row is performing.
Time to test. Place a Task Factory Terminator Destination below the Null Handler. Place a data viewer inline with it and run the package. Your results should be:
Well, this looks pretty good, but it’s not quite right. For the SomeText1 column, we wanted to check for two conditions, first if it’s null we wanted it converted to a value, second if it was blank we wanted it converted to a different value. In the first null transform we handled the conversion of null to a specific value, but now how to also check for a blank in the column and replace it? No problem, all we need to do is insert a second Null Transform between the first one and the Terminator destination.
Inside it, beside the SomeText1, select the action “Convert blank value to a user defined value”. For the user defined value, enter “Was Blank”. Now hook it to the destination, and place a data viewer on it. Now we should see the correct results.
There we go. The Null Transform makes dealing with blank and null values very easy and straightforward.
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.
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.
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.
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.
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.