SSIS Expression Examples for Dates, String Concatenation, Dynamic File Names and More

By:   |   Updated: 2021-10-26   |   Comments (1)   |   Related: More > Integration Services Development


I have heard that using SQL Server Integration Services (SSIS) expressions can help make SSIS packages more dynamic. Can you give me some ideas of how this can be done as well as some examples related to file names, strings, dates, concatenating values and more?


In this article we will show some typical SQL Server Integration Services (SSIS) expressions.


I am using SQL Server 2017 and SQL Server Data Tools (SSDT) for VS 2015. However, most of the expressions can be applied to any SSIS version.

SSIS Expression to Export Data to a File where the File Name is Dynamically Generated

Let's say that you have a Data Flow like this and you want to save the output to a file that includes the current date, something like this: report12-21-2018.csv.

SSIS OLEDB Source to Flat File

To do that, we will use the expressions in the Flat File connection.  Right click and select Properties and the click on Expressions in the Properties window as shown below.

Expression in SSIS flat file connection

In the Property Expressions Editor, select ConnectionString for the Property and in the Expression we will use the following SSIS expression:

"c:\\sql\\report" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "yyyy" , getdate() ), 2) +".csv"			
SSIS Connection string expression

Basically, the expression is creating a file in the c:\sql\ folder with the file name as "report" plus the current date plus .csv file to get report12-21-2018.csv. For example, if the current date is 12-21-2018, the file name will be report12-21-2018.csv.

  • When using \ it needs to be replaced with \\ because it is a special character.
  • The function getdate returns the current date. Datepart will give us part of the date. It will get the days (dd), months (mm) and year (yyyy). Basically, we will concatenate the report with the path the day, month and year and concatenate with the extension (csv).

SSIS Expression to Separate Full Name into First and Last Name

Let's say that you have a variable named fullName with the first and last name as shown below:

SSIS string variable

The first name and last name are separated by a space in the full name variable and we want to separate the value into first name and last name.  I will do this will additional variables and add firstName and lastName variables:

SSIS 2 string variables

To store the first name, we are going to use the Expression Task. This task is available in SQL Server 2014 and newer versions:

Expression Task in SSIS

The expression that we are going to use to get the first name is the following. Basically, we are storing into the variable firstname all letters starting from the LEFT until a space. The value of the variable will be "Raynor".

@[User::firstName] = LEFT(@[User::fullName],FINDSTRING( @[User::fullName] , " ", 1))			
SSIS expression to separate 2 words separate by spaces.

To get the last name, it is a little bit harder, but similar. We will use the following expression:

@[User::lastName] = RIGHT(@[User::fullName],LEN(@[User::fullName])-FINDSTRING( @[User::fullName] , " ", 1))

In this example we are reading from RIGHT to LEFT until the space. We use LEN to count the total number of characters minus the number of characters until the space. That will get the lastname.

These expressions show how to separate the value if there was just one space, so if you have more complex strings with more than one space you will need to add additional logic to break apart the variable.

SSIS Expression to Make First Letter Uppercase

The function UPPER is used to uppercase a letter. You will need to concatenate the first letter uppercased with the rest of the string (the second letter until the number of letters counted by the LEN function).

UPPER(SUBSTRING( @[User::firstName] , 1, 1)) + SUBSTRING( @[User::firstName] , 2, LEN( @[User::firstName])-1)

SSIS Expression to Remove Spaces from a String

To replace all spaces, you use the replace function:

REPLACE( @[User::fullName] , " ","")

To remove leading spaces, you can use LTRIM.

LTRIM(" Removes leading spaces")

To remove trailing spaces, you can use RTRIM.

RTRIM ("removes trailing spaces ")

To remove leading and trailing spaces, you can use TRIM.

TRIM(" Removes leading and trailing spaces ")

SSIS Expression to Create Dynamic Connection Manager

Please refer to the following article:

SSIS Expression to Create Variables

Please refer to the following article:

SSIS Expression Create Expressions Between Tasks

Let's say that we want to execute a task and based on the success or failure, we take a different path:

SQL Server Integration Services precedence constraints

To do this, refer to this article:

SSIS Expression to Concatenate Two Strings

To concatenate two strings, use the + operator:

"this is "+"how to concatenate"

SSIS Expression to Concatenate a String with a Number

If you try to concatenate a string with a number as follows:

"number of years "+23

You will receive an error message like this one:

The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation ""number of years " + 23" failed with error code 0xC0047080.

To concatenate a string with a number, we need to convert the number to string using the DT_WSTR. The (DT_WSTR,2) converts the number 23 into a string of length 2.

"number of years: "+ (DT_WSTR, 2) 23

SSIS Expression to Concatenate a String with a Date

This question is like the previous one. In this example, I will use a variable named date of type DateTime:

"number of years: " + ( DT_WSTR, 29) @[User::date]

Note that the length is 29, using a lower number will show the following error message:

A truncation occurred during evaluation of the expression. This occurs when the length is lower than 29:

SSIS Expression to Concatenate 2 variables with Google Maps Coordinates into a URL

Let's say that we have these coordinates in Google Maps:

Google coordinates

We have 3 variables with the following values: 48.8078895, 2.3416099 and 11.77 and we want to generate the following url:,2.3416099, 11.77z 

We will concatenate the string with the variables. To do that, we will convert the decimal variables to strings and concatenate:

"https:/"+ (DT_WSTR, 12) @[User::coordinate1]+", "+ (DT_WSTR, 12)@[User::coordinate2]+","+(DT_WSTR, 12) @[User::coordinate3]+"z" 

SSIS Expression to get Beijing (China) Time into a Variable

In order to get the time, you need to add 8 hours to the UTC time. There is no daylight saving time. You use the DATEADD function to add 8 ours and the GETUTCDATE shows the UTC datetime:


SSIS Expression to Get Time in the Format of hh:mm:ss

You can use the datepart function to concatenate hour, minutes and seconds:

RIGHT("0" + (DT_WSTR,2)DATEPART("hh", DATEADD("Hour", 8,GETUTCDATE())), 2) + ":"
 + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETUTCDATE()), 2) + ":"
 + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETUTCDATE()), 2)

Another way can be to convert the time to string and truncate the date and the milliseconds:


SSIS Expression to Handle the Daylight Savings Time

In our previous example, we used China which does not have daylight savings time. Many countries will set their clocks back an hour during a period of time. In this example, we will use Atlanta. The daylight savings time starts on March 3, 2019 and ends on November 3, 2019. We will set two variables with these dates:

start and end time for daylight saving

Once that you have the start date and end date, we will use the following expression:

(@[User::myDate]< @[User::startTime] || @[User::myDate]> @[User::startTime]) ? (DATEADD("Hour", -5,GETUTCDATE())):(DATEADD("Hour", -4,GETUTCDATE())) 

If the date (stored in the variable myDate) is outside the interval (mydate<startTime or mydate>starttime), we subtract 5 hours otherwise we subtract 4 hours. The "?" is a conditional and if the condition is true, then 5 hours are subtracted else just 4 hours.

SSIS Expression to Detect Leap Year

This one is easy. If the year%4 is 0, then it is a leap year otherwise it is a common year:

(2005 %4==0) ? "leap year":"common year"

SSIS Expression to get Age Based on Birth Date

The datediff function can get the difference in years (yy), months(mm), days (dd), etc. The following date obtains the difference in years between the current date and 8/1/2003.


SSIS Expression to handle numbers

Here you have some expressions to handle numbers, precision and concatenations.

Desc SSIS Expression Output
1 decimal (DT_DECIMAL,1)500 500.0
2 decimals (DT_DECIMAL,2)500 500.00
integer (DT_I4)500.25 500
Concatenation "The salary is "+(DT_WSTR,3)300+"$" The salary is 300$
Round up (DT_DECIMAL,2) ROUND(16.55,1) 16.60
Round down (DT_DECIMAL,2) ROUND(16.54,1) 16.50
Floor (DT_DECIMAL,2) FLOOR(20.55) 20.00
Ceiling (DT_DECIMAL,2) CEILING(20.55) 21.00

SSIS Expressions to work with strings

Here you have some useful string function examples commonly used in SSIS expressions. You can replace strings, lowercase, uppercase, repeat words, find the position of a character, reverse data, remove spaces and get substring from a string.

Desc SSIS Expression Output
Replace bikes with nothing REPLACE("Cars, bikes, motos", ", bikes","") Cars, motos
Lowercase LOWER("HELLO WORLD" ) hello world
Uppercase UPPER("hello world" ) HELLO WORLD
Replicate word 3 times REPLICATE("Hello ", 3) Hello Hello Hello
Find the position of the word California FINDSTRING("This is an example about California", "California", 1) 26
Reverse REVERSE( "Hello" ) olleH
TRIM Example is like LTRIM and RTRIM TRIM( " Sample text " ) Sample text
LTRIM Example to remove left spaces LTRIM( "     Sample text" ) Sample text
RTRIM Example to remove white spaces RTRIM( "Sample text    " ) Sample text
Get the substring starting on position 12 and get 10 characters SUBSTRING("The city is Manhattan",12,10)  Manhattan

SSIS Expressions to work with NULL values

Finally, we have some functions to convert to null values, detect if the value is null or to replace null values in our SSIS Expressions. Here you have some examples.

Desc SSIS Expression Output
Returns a null value NULL(DT_UI8) 0
If the value is null returns true ISNULL( NULL(DT_DECIMAL, 2)) True
If the value is not null, returns false ISNULL( 3.25) False
Replaces a NULL value to 0 REPLACENULL( (NULL(DT_DECIMAL, 2)), 0 ) 0.00


In this article, we saw useful examples of SSIS expressions. SSIS expressions enrich the functionality and help with dynamic customization of your packages.

Next Steps

If you have more questions, feel free to ask in the comments section.

For more information refer to these links:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-10-26

Comments For This Article

Monday, August 12, 2019 - 2:31:28 PM - Joe Mineo Back To Top (82040)

FYI: the formula for determining if a year is a leap year is not that simple.

A year may be divisible by 4 and still not be a leap year. for completeness, you have to consider
years that are divisible by 4 and 100 (not a leap year) or divisible by 4 AND divisible by 400 (is a leapyear)

if ((year % 4 ==0) && ((year % 100 != 0)||(year % 400 == 0)) then leapyear = true else leapyear = false

get free sql tips
agree to terms