Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2019-01-22   |   Comments   |   Related Tips: More > Integration Services Development

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.

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:

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:

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.

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

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:



Last Updated: 2019-01-22


next webcast button


next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools