• Home
  • SSIS
  • SQL Server Concatenate In SSIS

SQL Server Concatenate In SSIS

  • (4.0)
  • | 3515 Ratings

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!

Learn how to use SSIS, from beginner basics to advanced techniques. Enroll for Free SSIS Training Demo!

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:

Subscribe For Free Demo

Free Demo for Corporate & Online Trainings.

Ravindra Savaram
About The Author

Ravindra Savaram is a Content Lead at His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter. Protection Status