By: Daniel Calbimonte | Updated: 2019-01-22 | 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.
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:
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:
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:
We have 3 variables with the following values: 48.8078895, 2.3416099 and 11.77 and we want to generate the following url:
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:
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() )
In this article, we saw useful examples of SSIS expressions. SSIS expressions enrich the functionality and help with dynamic customization of your packages.
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)
Last Updated: 2019-01-22
About the author
View all my tips