SSIS Expression Examples - TechNet Articles - United States (English)

Do you have examples of SSIS expressions that you want to share? Consider adding them to this article, or just link to examples you've already published.

In Integration Services, expressions can be used to define conditions for CASE statements, create and update values in data columns, assign values to variables, update or populate properties at run time, define constraints in precedence constraints, and provide the expressions used by the For Loop container. Expressions can be used in the Conditional Split transformation, Derived Column transformation, the For Loop container, variables, and properties of the package.

With the expression results of DT_STR or DT_WSTR data type, the results are limited to 4000 characters till SQL Server 2008 R2. This limit has been removed in SQL Server 2012.

For more information about expressions in packages, see these Books Online articles.

For additional examples of expressions, see:

SSIS Expression Cheat Sheet by Pragmatic Works
sqlstuff.weebly.com/ssis.html (Explains how to use FINDSTRING, SUBSTRING, and RIGHT within the Derived Column Transformation editor to clean up concatenated strings from SharePoint lists when importing the strings to SQL 2008.)
SSIS Expression Language and the Derived Column Transformation
Use Property Expressions in Packages (see Sample Property Expressions section)

SSIS Design Patterns Wiki:
http://social.technet.microsoft.com/wiki/contents/articles/10844.ssis-design-patterns.aspx

Here are a few examples of expressions from our Integration Services (SSIS) documentation in Books Online.

Example	Description
(DT_DBTIMESTAMPOFFSET,3) "1999-10-11 20:34:52.123 -3:30" != (DT_DBDATE)"1999-10-12"	The system converts the expression,(DT_DBDATE)"1999-10-12", to DT_DBTIMESTAMPOFFSET. The example evaluates to TRUE because the converted expression becomes "1999-10-12 00:00:00.000 +00:00", which is not equal to the value of the other expression,(DT_DBTIMESTAMPOFFSET,3) "1999-10-11 20:34:52.123 -3:30".
(DT_I4) 3.57	Casts a numeric value to an integer.
(DT_STR,1,1252)5	Casts an integer to a character string using the 1252 code page.
(DT_WSTR,3)"Cat"	Casts a three-character string to double-byte characters.
(DT_DECIMAl,2)500	Casts an integer to a decimal with a scale of two.
(DT_NUMERIC,7,3)4000	Casts an integer to a numeric with a precision of seven and scale of three.
(DT_STR,50,1252)FirstName	Casts values in the FirstName column, defined with an nvarchar data type and a length of 50, to a character string using the 1252 code page.
(DT_BOOL)"True"	Casts the string literal "True" to a Boolean.
(DT_DBDATE) "1999-10-11"	Casts a string literal to DT_DBDATE.
(DT_DBTIME2, 5) "16:34:52.12345"	Casts a string literal to the DT_DBTIME2 data type that uses 5 digits for fractional seconds. (The DT_DBTIME2 data type can have between 0 and 7 digits specified for fractional seconds.)
(DT_DBTIMESTAMP2, 4) "1999-10-11 16:34:52.1234"	Casts a string literal to the DT_DBTIMESTAMP2 data type that uses 4 digits for fractional seconds. (The DT_DBTIMESTAMP2 data type can have between 0 and 7 digits specified for fractional seconds.)
(DT_DBTIMESTAMPOFFSET, 7) "1999-10-11 16:34:52.1234567 + 5:35"	Casts a string literal to the DT_DBTIMESTAMPOFFSET data type that uses 7 digits for fractional seconds. (The DT_DBTIMESTAMPOFFSET data typecan have between 0 and 7 digits specified for fractional seconds.)
DATEADD("Month", 1,GETDATE())	Adds one month to the current date.
DATEADD("day", 21, ModifiedDate)	Adds 21 days to the dates in theModifiedDate column.
DATEADD("yyyy", 2, (DT_DBTIMESTAMP)"8/6/2003")	Adds 2 years to a literal date.
DATEDIFF("dd", (DT_DBTIMESTAMP)"8/1/2003", (DT_DBTIMESTAMP)"8/8/2003")	Calculates the number of days between two date literals. If the date is in "mm/dd/yyyy" format, the function returns 7.
DATEDIFF("mm", (DT_DBTIMESTAMP)"8/1/2003",GETDATE())	Returns the number of months between a date literal and the current date.
DATEDIFF("Week", ModifiedDate,@YearEndDate)	Returns the number of weeks between the date in theModifiedDate column and theYearEndDate variable. IfYearEndDate has a date data type, no explicit casting is required.
DATEPART("month", (DT_DBTIMESTAMP)"11/04/2002")	Returns the integer that represents the month in a date literal. If the date is in mm/dd/yyyy" format, this example returns 11.
DATEPART("dd", ModifiedDate)	Returns the integer that represents the day in theModifiedDate column.
DATEPART("yy",GETDATE())	Returns the integer that represents the year of the current date.
DAY((DT_DBTIMESTAMP)GETDATE())==1\|\|DAY((DT_DBTIMESTAMP)GETDATE())==15?1:2	Can be used to set the LoggingMode property of a package. The expression uses the DAY and GETDATE functions to get an integer that represents the day datepart of a date. If the day is the 1st or 15th, logging is enabled; otherwise, logging is disabled. The value 1 is the integer equivalent of theLoggingMode enumerator member Enabled, and the value 2 is the integer equivalent of the member Disabled. You must use the numeric value instead of the enumerator member name in the expression.
"PExpression-->Package: (" + @[System::PackageName] + ") Started:" + (DT_WSTR, 30) @[System::StartTime] + " Duration:" + (DT_WSTR,10) (DATEDIFF( "ss", @[System::StartTime] , GETDATE() )) + " seconds"	Can be used to set the Subjectproperty of a Send Mail task and provide a useful e-mail subject. The expression uses a combination of string literals, system variables, the concatenation (+) and cast operators, and the DATEDIFF and GETDATE functions. The system variables are the PackageNameand StartTime variables.
"Rows Processed: " + "\n" +" NASDAQ: " + (dt_wstr,9)@[nasdaqrawrows] + "\n" + " NYSE: " + (dt_wstr,9)@[nyserawrows] + "\n" + " Amex: " + (dt_wstr,9)@[amexrawrows]	Can be used to set theMessageSource property of a Send Mail task. The expression uses a combination of string literals, user-defined variables, and the concatenation (+) operator. The user-defined variables are namednasdaqrawrows,nyserawrows, andamexrawrows. The string "\n" indicates a carriage return.
DATEPART("weekday", GETDATE()) ==2?"notepad.exe":"mspaint.exe"	Can be used to set theExecutable property of an Execute Process task. The expression uses a combination of string literals, operators, and functions. The expression uses the DATEPART and GETDATE functions and the conditional operator.
(DATEPART("DW",GETDATE()) == 7) ? "SUNDAY" : (DATEPART("DW",GETDATE()) == 1) ? "MONDAY" : (DATEPART("DW",GETDATE()) == 2) ? "TUESDAY" : (DATEPART("DW",GETDATE()) == 3) ? "WEDNESDAY" : (DATEPART("DW",GETDATE()) == 4) ? "THURSDAY" : (DATEPART("DW",GETDATE()) == 5) ? "FRIDAY" : (DATEPART("DW",GETDATE()) == 6) ? "SATURDAY" : "NULL"	Weekday name

| |

Comments

DMigliore

21 Feb 2012 1:13 PM

Explains how to use FINDSTRING,SUBSTRING,and RIGHT within the Derived Column Transformation editor to clean up concatenated strings from SharePoint lists when importing the strings to SQL 2008. sqlstuff.weebly.com/ssis.html
Carla Sabotta

8 Aug 2012 2:07 PM

Thank you DMigliore for adding the link to this blog post. I've included it in the article above.
Aalamjeet Rangi

13 Apr 2013 6:33 PM

I've tried reformatting the table multiple times but it does not render correctly on the Chrome browser. I've reported the issue to Google. It looks fine in IE9.
Payman Biukaghazadeh

2 May 2013 6:31 AM

Thanks
Sardys

10 Sep 2014 12:07 PM

Pragmaticworks Updated new link for the SSIS Expressions Cheat Sheet is: pragmaticworks.com/.../SSIS-Expression-Cheat-Sheet

Thank you
ktrock

26 Jun 2015 12:12 PM

(DT_WSTR, 4) DATEPART("yy",GETDATE()) + RIGHT( "0" + (DT_WSTR, 2) DATEPART("mm",GETDATE()), 2) + RIGHT( "0" + (DT_WSTR, 2) DATEPART("dd",GETDATE()), 2)

Returns yyyymmdd as a string in case you need to combine it with a SELECT as in "SELECT * FROM MyUDF(@[User::Today])"