SQL Server Concatenate In SSIS

String Concatenation

There are many uses for building strings within an expression. Strings are built to represent a SQL statement that can be executed against a database, to provide information in the body of an e-mail message, or to build file paths for file processing. Building strings is a core task that you have to be able to do for any development effort. In SSIS the concatenation operator is the plus (+) sign. Here is an example that you can quickly put together in the Expression Builder and test:

“The Server [” + LOWER( @[System::MachineName]) + “] is running this package”

This returns the following string:

The Server [myserver] is running this package

If you need to build a string for a file path, use the concatenation operator to build the fully qualified path with the addition of an escape character to add the backslashes. Later in this Using Variables, Parameters, and Expressions Topic, the section “String Literals” covers all the common escape characters that you’ll need for string building. A file path expression would look like this:

“c:mysourcefiles” + @myFolder + “” + @myFile

Note that strings are built using double quotes (“”), not single quotes (”) as you might see in T-SQL; it’s important to ensure that the strings are all Unicode or all non-Unicode. A previous limitation of 4,000 characters for an expression has been removed from Integration Services. Feel free to make strings as long as you desire!

Line Continuation

There are two reasons to use line continuation characters in SSIS expressions. One is to make the expression easier to troubleshoot later, and the other is to format output for e-mail or diagnostic use. Unfortunately, the expression language does not support the use of comments, but you can use the hard returns to help the expression look more organized. In the Expression Builder, simply press the Enter key to have your expression displayed with the carriage-return-line-feed character sequence. This formatting is maintained even after you save the expression. To format output of the expression language, use the C-like escape character n. Here’s an example of using it with a simple expression:

“My Line breaks herenAnd then heren; )”

This returns the following string:
My Line breaks here
And then here
; )
Note that it is not necessary to show the expression in code form in one line. An expression can be written on multiple lines to clarify viewing of it at design time. The output would remain the same.

Frequently Asked SSIS Interview Questions & Answers

List of Related Microsoft Certification Courses: