Handling Escape Sequences in SSIS Expression String Literals

The SSIS expression language is a powerful tool for a developer that gives you one method of making a package dynamic.  When writing an expression there are a few things to be careful of that could potentially cause a stumbling block while writing code. 

For example, a common reason you may write expressions is to populate a changing file name.  Say you want to have a file loaded but the name of the file should change each day the SSIS package is ran to have the current date appended to the end of the file name.  This could be accomplished several ways but one method may use an expression similar to this:

"C:\\Test\\File"+ (DT_WSTR, 10)(DT_DBDATE) GETDATE()+".txt"

When an expression like this is evaluated then the results may look like this (depending on the date):

C:\Test\File2010-01-27.txt

In this example the hardcoded string values are identified by the text that is between the quotations marks.  One unusual thing about this expression is the double backslashes, which in the evaluation represents a single backslash.  This is known as an escape sequence meaning that a single backslash really represents something else.  So the purpose of this post is to warn you about these escape sequences in string literals that you will encounter. 

The example shown here is a very common example that many people know about but there are several other escape sequences that you should know about as well and can be found on msdn.  Here’s a copy of the chart from that link showing all the escape sequences.

So if I needed to have quotation marks in my expression then I would use the \” to return back quotation marks in my hardcoded part of the expression:

"Devin Said,\"What's the deal with these escape sequences\""

This expression would evaluate to look like this:

Devin Said,"What's the deal with these escape sequences"