Typically, the data for an enterprise data warehouse does not arrive perfect. In fact, the data will come from many source and in many formats. One issue you will always have to contend with is missing values in the data.
Task Factory from Pragmatic Works has a transform to help you deal with those missing values, the SSIS NULL Handler Transform. Recently, I decided to try out this transform to see just what it could do.
I created a new 2012 SSIS package and added a connection manager to the project pointing to AdventureWorks2012. I added a Data Flow task. Inside the Data Flow task, I added an OLEDB source and configured it to point to the Production.Product table.
I then added a TF NULL Handler transform and connected the OLDEDB source to it. Since I was just interested in learning how this transform works, I connect the transform to a Union All transform.
The NULL Handler's property dialog box looks like this
For each input column, you can either choose No Action, Convert NULL values to user defined value, Convert blank to NULL or Convert blank value to user defined value. If I select either of the “user defined value” choices, I am given the opportunity to supply that value, which I can type in or select a variable or another column.
For output, I can either replace the existing column or create a new column.
Another interesting feature is the ability to configure a rule to multiple columns at once to save time if there is going to be many columns with NULLs.
Now to test transform. I enable a Data Viewer and run the package.
It worked just as advertised! The Task Factory NULL Handler transform is easy to use and takes care of those pesky NULLs!