Replace Null Transformation a.k.a. the defaulter

An often used task in a data flow task is to replace all null values for dimension lookups. If you're lucky you can use the short 2012 expression REPLACENULL or else you're stuck with ? :


Replace Null

Doing that a dozen times for each fact package over and over is boring, so my collegue Marc Potters developped a Replace Null Transformation (a.k.a. Defaulter Transformation) that can replace null values in several columns at once. We made it a bit more general so it can be used to replace null values for all data types.

Replace Null

Disclaimer
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Downloads:
You can download a SSIS 2008 and 2012 version on the download page.

BETA2: solved bug that could result in double output.
BETA3: solved bug that could result in value 0 if only one column is selected.

Installation
The installer registers the DLL in the GAC and copies it to the component folder of SSIS (example: C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents\). After that you have to close Visual Studio/BIDS because it caches the GAC on startup. (Restart not required for SSDT)

How add the transformation to the toolbox (2008 only)
When you open a SSIS project in Visual Studio/Bids, right click the toolbox and choose "Choose Items...". After this Visual Studio will be busy for a couple of seconds!

Right click toolbox

When the Choose Toolbox Items window appears, go to the SSIS Data Flow Items and search for the newly installed transformation and select it. Click ok to finish.

Choose Toolbox Items

Now the new transformation will appear in the toolbox. Ready to use! Have fun.


New component added



Bugs or feature suggestions
Please contact me or leave a comment if you have any suggestions or found a bug in this Custom component.