Task Factory Trim Plus Transform

I’m beginning to realize that I really like to do things the easy way.  Of course I always want things done right, but the easier the better, which is why I am always thankful that I get to use the Pragmatic Works Software Suite while I am developing SSIS Packages.  Task Factory is very helpful in the mission to make my life easier.  It has 26 tasks and growing with every release.

One of the consistent problems that I run into while developing ETL packages is cleansing the data.  Sometimes strange or unwanted characters creep up in the data or leading and trailing spaces need to be removed before landing the data in a new table, and Task Factory has a transform that can help you quickly get rid of the unwanted characters or space called the Trim Plus Transform.

The steps for configuring this transform are quick and easy.

Connect your source data to the transform.

Double click to open up the transform editor.

Now you can select what actions you want to perform on each column. There are several actions to choose from in that drop down.

1. Trim all leading and trailing white-space characters

2. Trim all leading white-space characters

3. Trim all trailing white-space characters

4. Trim specified characters from start and end

5. Trim specified characters from start

6. Trim specified characters from end

You can specify what characters you want to trim in the last 3 actions. Certain characters are difficult for one reason or another but the transform is built to handle them using an escape sequence (e.g. Tab [\t], CarriageRerurn [\r], LineFeed [\n], VerticalTab [\v], SingleQuote [\'], DoubleQuotes [\"], NullCharacter [\0])

You can also specify if you want to replace or add a new column as the out put action.

If you look at the figure above you can see that I’ve set the FeetInches column I am choosing to get rid the extra double quote (“). The data looks like this going in.

then using the \” to indicate the “ as the character I want to remove I run the package and here is my result.

As you can see it removed the unwanted characters and now my data is ready to land in my table.

If you get a chance take a look at Task Factory it has 26 transforms designed to assist your development.

Well that’s all I have time for today, since its Friday. Have a good weekend and good luck in your development adventures.