SSIS comes with an Expressions language that can be used to assign values to variables and properties of packages and tasks at runtime. Expressions can be built in some cases using the GUI Expression Builder module, but in other parts of SSIS require direct input. The Expression Builder includes an 'Evaluate Expression' button, which you can use to test the return value of an expression.

Variables are scoped at the level of the selected object at their time of creation. To create a package variable, right-click the Control Flow design surface and select 'Variables...' from the context Menu. To create a task-scoped variable, select the task, then click the 'Add Variable' button in the 'Variables' pane. To see the Properties page for a selected variable, press 'F4'.

Expression Snippets

Returns a 4-byte unsigned integer - the default type for a numeric literal value without a decimal place

32

Returns a 4-byte signed integer

(DT_I4)32

Returns a 8-byte signed integer

(DT_I8)32

Returns a 8-byte unsigned integer

(DT_UI8)32

Returns a 8-byte signed integer

32L

Returns a 8-byte unsigned integer

32UL

Returns a decimal value of type DT_NUMERIC - the default for numeric literal with decimal place

32.3

Returns a real or float value

32.3f

Returns a Unicode string value

"Hello"

Returns two-character, non-Unicode string, using the 1252 codepage

 (DT_STR, 2,1252)32

Returns a four byte-pairs (eight bytes) Unicode string

(DT_WSTR, 4))32

Returns a file path of C:\TEMP\d.txt where 'd' is the day of the week

"C:\\TEMP\\" + 
(DT_WSTR, 4)(DATEPART("dw",GETDATE())) + 
".TXT"

Returns a file path of C:\TEMP\dd.txt where 'dd' is the two-digit, zero-padded day of the week

"C:\\TEMP\\" + 
RIGHT( "0" + (DT_WSTR, 2)(DATEPART("dw",GETDATE())),2 ) + 
".TXT"

Returns a file path of "C:\TEMP\7.TXT, where @[User::wrkdir] holds the value "C:\TEMP\"

@[User::wrkdir]  + 
(DT_WSTR, 1)( DATEPART("dd",( (DT_DATE)"2010-05-07" )) ) + 
".TXT"

Returns a file path of "C:\TEMP\9.TXT, where POWER(3,2) represents 3 to the power of 2

 "C:\\TEMP\\"  + 
(DT_WSTR, 1)(POWER(3,2)) + 
".TXT"

Returns a file path of "c:\temp\9.txt

LOWER( "C:\\TEMP\\"  + 
(DT_WSTR, 1)(POWER(3,2)) + 
".TXT" )

Returns 'Wednesday' on Wednesday or 'Not Wednesday' on any other day

DATEPART("dw", GETDATE()) == 3 ? "Wednesday"  : "Not Wednesday"

Returns True on Wednesday or False on any other day

DATEPART("dw", GETDATE()) == 3

Returns True if PersonID column in data flow is equal to 5

PersonID == 5

Returns True if PersonID equals value in @[User::wanted] variable

PersonID == @[User::wanted]

Returns True if PersonID equals 5 and today is Wednesday

PersonID == 5 && DATEPART("dw", GETDATE()) == 3

Returns True if PersonID equals 5 or today is Wednesday

PersonID == 5 || DATEPART("dw", GETDATE()) == 3

Returns True if PersonID equals 5 and today is not Wednesday

PersonID == 5 && DATEPART("dw", GETDATE()) != 3

Returns true if the @wrkdir variable contains an empty string, otherwise False

@[User::wrkdir] == ""

Returns value of DEFAULT_VALUE if DATA_COLUMN contains a NULL value

IsNull(DATA_COLUMN) ? DEFAULT_VALUE : DATA_COLUMN

Returns a NULL 255-character, Unicode string if DATA_COLUMN value begins with 'B'

SUBSTRING([DATA_COLUMN], 1,1) == 'B' ? 
NULL(DT_WSTR, 255) : [DATA_COLUMN]

Returns a NULL 255-character, non-unicode string if DATA_COLUMN value begins with 'B'. Note the double-casting required for non-Unicode strings

SUBSTRING([DATA_COLUMN], 1,1) == 'B' ? 
(DT_STR, 255, 1252)NULL(DT_STR, 255, 1252) : [DATA_COLUMN]

Returns the string '01'

SUBSTRING("0" + "11", 1,2)

Returns 'io', two characters beginning from the fifth character in the string

SUBSTRING("Ameliorate", 5,2)

Returns 'Outer'

Trim("   Outer   ")

Returns 'Outer '

RTrim("   Outer   ")

Returns ' Outer'

LTrim("   Outer   ")

Returns 10

LEN("   Outer   ")

Returns 5

LEN(TRIM("   Outer   "))

Returns SQL Statement: "SELECT * FROM Customers WHERE dob = '2001-11-05' "

"SELECT * FROM Customers WHERE dob = '" + 
"2001-11-05" + "'"

Returns SQL Statement: "SELECT * FROM Customers WHERE dob = '2001-11-05' "

"SELECT * FROM Customers WHERE dob = '" + 
(DT_WSTR, 255)@[User::birthday] + "'"