SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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):


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.

Escape sequence







Form feed


New line


Carriage return


Horizontal tab


Vertical tab


Quotation mark




Unicode character in hexadecimal notation

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"

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).


Posted by Tim Mitchell on 28 January 2010

Good writeup, Devin.  Expressions remain a mystery to some folks, so this kind of information is valuable for those getting started with SSIS.

Leave a Comment

Please register or log in to leave a comment.