SSIS Expressions for Dates, String Concatenation, Dynamic File Names

Problem

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?

Solution

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

Requirements

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.

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.

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"

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:

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:

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

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

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:

• Expression for SSIS Connection Manager

SSIS Expression to Create Variables

Please refer to the following article:

• SSIS Expression for Variables

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:

To do this, refer to this article:

• Using Expressions On Precedence Constraints in Integration Services

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:

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

https://www.google/maps/@48.8078895,2.3416099, 11.77z 

We will concatenate the string https://www.google/maps/@ with the variables. To do that, we will convert the decimal variables to strings and concatenate:

"https:/www.google/maps/@"+ (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:

DATEADD("Hour", 8,GETUTCDATE()) 

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:

LEFT(RIGHT( (DT_WSTR, 29) DATEADD("Hour", 8,GETUTCDATE()),18 ),8)

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:

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.

DATEDIFF("yy", (DT_DBTIMESTAMP)"8/1/2003", GETDATE() )

SSIS Expression to handle numbers

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

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.

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.

Conclusion

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:

• REPLACE (SSIS Expression)
• RTRIM (SSIS Expression)
• GETDATE (SSIS Expression)
• TRIM (SSIS Expression)
• LTRIM (SSIS Expression)
• UPPER (SSIS Expression)
• DATEPART (SSIS Expression)
• Operators (SSIS Expression)
• ? : (Conditional) (SSIS Expression)
• <= (Less Than or Equal To) (SSIS Expression)
• DATEADD (SSIS Expression)
• DATEDIFF (SSIS Expression)