Easily Create COALESCE in SSIS Expressions

Caveat: REPLACENULL was introduced in SSIS 2012. For earlier versions, ISNULL is the best option.

There's no COALESCE function within SSIS expressions, nor a way to achieve it without using nested statements. The built-in ISNULL function works well for one or two null evaluations or to perform additional logic based on null values, but becomes syntactically difficult to write and read for COALESCE type operations. REPLACENULL does the trick a little more cleanly. Below is an example:

REPLACENULL([Column1],REPLACENULL([Column2],REPLACENULL([Column3],"Unknown")))

Add REPLACENULL([ColumnN], for each column. The final mention of your columns should be followed by your default value when all of the columns are null (e.g. NULL, "Unknown", 0). Close the whole statement by adding the same number of parentheses after the default value as the number of columns evaluated in the statement (in this case, three).

Essentially, you're telling SSIS to replace the null in the first column with the second column, which is also checks to replace the second column's null with the third column. When all of these have been checked and all evaluate to null, the default value is set.

Comments

Post a Comment

Popular posts from this blog

Continuously Monitor Folder for New Files with PowerShell as a WindowsService

Recently encountered an old-school application that used a cobbled-together external service to generate reports and deliver to end-users in PDF format. Unfortunately, this service didn't have any error handling other than dropping the intended source content into a flat file in an error directory on the local disk. No monitoring, no notification, just fail and pretend it never happened. As these reports are critical to end users, I needed a way to monitor this directory for any new files and notify someone so that the report could be manually re-generated. After some initial Googling, I decided a quick PowerShell script would be the easiest method to continuously monitor the directory and fire off a notification when a new file is found. I'm admittedly an extreme PowerShell newbie, so I'm sure there are better, cleaner ways to do this, but this works nicely and is stable enough for now until my legacy system is retired in a few months. The below PowerShell script will inst...
Read more

Unprotect All Excel Sheets for All Workbooks in a Directory

This Excel macro came in very handy when I had a set of workbooks in a folder (and subfolders) that all needed unprotected before running another macro to combine all of the workbooks into a single file. Be sure to update the bolded variables below.  Source . Const cStartFolder = " C:\Documents and Settings\UserName\My Documents\Spreadsheets " 'no slash at end Const cFileFilter = " *.xlsx " Const cPassword = " CleverPassword " 'use empty quotes if blank Sub UnprotectAllWorksheets() Dim i As Long, j As Long, arr() As String, wkb As Workbook, wks As Worksheet ExtractFolder cStartFolder, arr() On Error Resume Next j = -1: j = UBound(arr) On Error GoTo 0 For i = 0 To j Set wkb = Workbooks.Open(arr(i), False) For Each wks In wkb.Worksheets wks.Unprotect cPassword Next wkb.Save wkb.Close Next End Sub Sub ExtractFolder(Folder As String, arr() As String) Dim i As Long, objF...
Read more

ChromeOS Flow by Hexxeh Install to HDD

I finally decided to pull out the netbook again and check out the ChromeOS release of Flow by Hexxeh . I got the image downloaded, untar'd and imaged to the thumbdrive. Now comes the part when I want to actually install it to the solid-state disk in my Asus EeePC, right? Well, online sources said to just run /usr/sbin/chromeos-install and it would be taken care of. It kept erroring with "Attempt to install to a removable device: /dev/sda". Of course, this is my thumbdrive so that would make sense. Since it took me some digging to figure this one out, I thought I'd throw up the solution here to make it a little more visible on the Interwebs. Instead, run this command: /usr/sbin/chromeos-install /dev/sdb This should work if your internal device is at least 8gb. Unfortunately for me, mine is 4gb and my source media is 2gb. Instead, I ended up just copying the entire live image from my thumbdrive to the hard drive. Whatever works, right? Run this command instead of the ab...
Read more