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!

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:

MindMajix Youtube Channel

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

 SSRS Power BI
 SSAS SQL Server
 SCCM SQL Server DBA
 SharePoint BizTalk Server
 Team Foundation Server BizTalk Server Administrator

 

Course Schedule
NameDates
SSIS TrainingNov 09 to Nov 24View Details
SSIS TrainingNov 12 to Nov 27View Details
SSIS TrainingNov 16 to Dec 01View Details
SSIS TrainingNov 19 to Dec 04View Details
Last updated: 27 Sep 2024
About Author

I am Ruchitha, working as a content writer for MindMajix technologies. My writings focus on the latest technical software, tutorials, and innovations. I am also into research about AI and Neuromarketing. I am a media post-graduate from BCU – Birmingham, UK. Before, my writings focused on business articles on digital marketing and social media. You can connect with me on LinkedIn.

read less
  1. Share:
SSIS Articles