SSIS Tip: Formatting Numbers with Commas via Expression

Oh, Microsoft - why do you make us cry?

And no, I am not talking about Windows 10. Though that does make me cry a bit. Every day.

But enough about me.

You (sound developer that you are) have built a sweet new SQL Server Integration Services (SSIS) based extract that does all sorts of cool things:

• Generates and writes out a bunch of files for a vendor or another system
• Captures the number of rows of data processed in each file
• Builds and formats an email message in an expression, which summarizes the files and data rows processed
• Emails the notification message to your key users

(Hopefully you sped up this whole process by using our handy Iatric Systems SSIS Template from a previous tip!)

But… you notice when you constructed the email that you had to convert the row-count variable(s) into a string (by using a CAST) to embed in the message like this:

"Good Morning,\r\n\r\n"
+"\t"+@[User::ExtractName]+" files have been saved to "+ @[User::ExtractPath]+" and are ready to upload to the vendor:"
+"\r\n\r\n"
+"\t"+@[User::DemoFile]+"
(Rows="+(DT_WSTR,18)@[User::DemoRowsNumber]+")"
+"\r\n\r\n"
+"\r\nThanks, the "+ @[User::SiteName] +" Data Administration Team"

(Wait a minute, what are those \r\n’s and \t’s? They are “escaped” control characters (or Literals) to get line breaks and tabs into the email message for better formatting.)

 …and when you did that you got just a plain ole number string:

Good Morning,
      (Billing Vendor Name) files have been saved to 
D:\Extracts\ExtractName and are ready to upload to the vendor:

      D:\Extracts\ExtractName\demo_2016_01_26_to_2016_01_26.txt (Rows=123456789)

Thanks, the (Your Site Name) Data Administration Team

And while that works, it doesn’t look so great, does it? We really want a number that looks like this:

123,456,789

Which is much more readable to our human eyes! But by default, there is no obvious way to display that number string with commas in an SSIS Expression. There’s no FORMAT() function, for example.

We have to create a way for that formatting to happen, and while you can do it with a Script Task (and a tiny bit of .NET) or by pumping the variable through SQL, I prefer keeping everything in package variables and their associated expressions because it’s easier for maintenance.

We do this by adding a matching string variable to our @User::[DemoRowsNumber] variable called:

@User::[DemoRowsFormatted]

And then we set the EvaluateAsExpression property to True for that variable and use an expression like this one to turn the number into a formatted string:

REVERSE(
SUBSTRING(
REVERSE((DT_WSTR,18)@[User::DemoRowsNumber])
,1,3)
+(LEN((DT_WSTR,18)@[User::DemoRowsNumber])>3 ?
","+SUBSTRING(
REVERSE((DT_WSTR,18)@[User::DemoRowsNumber])
,4,3) : "" )
+(LEN((DT_WSTR,18)@[User::DemoRowsNumber])>6 ?
","+SUBSTRING(
REVERSE((DT_WSTR,18)@[User::DemoRowsNumber])
,7,3) : "" )
+(LEN((DT_WSTR,18)@[User::DemoRowsNumber])>9 ?
","+SUBSTRING(
REVERSE((DT_WSTR,18)@[User::DemoRowsNumber])
,10,3) : "" )
+(LEN((DT_WSTR,18)@[User::DemoRowsNumber])>12 ?
","+SUBSTRING(
REVERSE((DT_WSTR,18)@[User::DemoRowsNumber])
,13,3) : "" )
+(LEN((DT_WSTR,18)@[User::DemoRowsNumber])>15 ?
","+SUBSTRING(
REVERSE((DT_WSTR,18)@[User::DemoRowsNumber])
,16,3) : "" ))

This expression works because when SUBSTRING() tries to get a part of the CAST’ed string that does not exist (because, say, the number string is only seven characters long and it’s trying to grab positions 13 through 15), the function returns an empty string ( ‘’ ) instead of a NULL. So the whole recreated string does not mysteriously vanish.

Note also that we do have REVERSE(), which lets us flip the string around and work place-wise from right to left.

Then we change the code for our email message just a smidge:

"Good Morning,\r\n\r\n"
+"\t"+@[User::ExtractName]+" files have been saved to "+ @[User::ExtractPath]+" and are ready to upload to the vendor:"
+"\r\n\r\n"
+"\t"+@[User::DemoFile]+" (Rows="+@[User::DemoRowsFormatted]+")"
+"\r\n\r\n"
+"\r\nThanks, the "+ @[User::SiteName] +" Data Administration
Team"

Which then gets us output like this:

Good Morning,
      (Billing Vendor Name) files have been saved to D:\Extracts\ExtractName and are ready to upload to the vendor:

      D:\Extracts\ExtractName\demo_2016_01_26_to_2016_01_26.txt (Rows=123,456,789)

Thanks, the (Your Site Name) Data Administration Team

Perfect!

Now you just add …RowsFormatted variables per row-counter you set up, copy the base expression, do a search and replace to swap out the number variable, and paste! And you’re done.

Extra Credit

If you’re dealing with numbers greater than 18 places long, add more sections.

Update your SSIS template (you have one of those you always start from, right?) with this change, and you’ll reduce the amount of cutting and pasting in your future.

If you don’t use Notepad++, give it a try. Adding the TextFX plug allows you to do all kinds of helpful manipulations of your code. In a complex SSIS expression, I’ll often write the expression in Notepad++ and then paste into the Expression Editor.

An alternate expression editor is also available with the BIDS Helper plugin for the SSIS development environment.

If you need more help…

Our Report Writing team can help you fix reports, create new ones, and make old ones faster. Simply reach out to your Iatric Systems Account Executive or our NPR report writing team at reportwriting@iatric.com to discuss how we can help support your team!