SSIS Expression Cheat Sheet

SSIS Expression Cheat Sheet
Problems	Expression
Create a file name with today's date	Expression on the Flat File or File Connection Manager: "C:\\Project\\MyExtract" + (DT_WSTR, 30)(DT_DBDATE)GETDATE() + ".csv" Expression Output Example: C:\Project\MyExtract2009-03-20.csv
Use a 2 digit date (ex. "03" for March instead of "3")	RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()),2) Expression Output: 03 (if the month is March)
Multiple condition if statement	In this example, the statement determines that if the ColumnName is blank or NULL, it will be set to unknown. To make a Logical AND condition, use "&&" instead of the "||" operator. ISNULL(ColumnName)||TRIM(ColumnName)==""?"Unknown": ColumnName
Returns the first five characters from a zip code	Derived Column Transform in the Data Flow: SUBSTRING(ZipCodePlus4,1,5)
Remove a given character from a string (ex. Remove "-" from a social security number)	Derived Column Transform in the Data Flow: REPLACE(SocialSecurityNumber, "-","")
Uppercase data	Derived Column Transform in the Data Flow: UPPER(ColumnName)
Replace NULL with another value	Derived Column Transform in the Data Flow: ISNULL(ColumnName)?"New Value" : ColumnName
Replace blanks with NULL values	Derived Column Transform in the Data Flow: TRIM(ColumnName)=="" ? (DT_STR, 4, 1252)NULL(DT_STR, 4, 1252) : ColumnName
Remove any non-numeric data from a column	Script Transform in the Data Flow Task with the code as follows (VB 2008): Imports System.Text.RegularExpressions Public Overrides Sub Input()_ProcessInputRows(ByVal Row As Input()Buffer) If Row.ColumnName_IsNull = False Or Row.ColumnName = "" Then Dim pattern As String = String.Empty Dim r As Regex = Nothing pattern = "[^0-9]" r = New Regex(pattern, RegexOptions.Compiled) Row.ColumnName = Regex.Replace(Row.ColumnName, pattern, "") End If End Sub
Convert text to proper case (ex. 1st letter in each word is uppercase)	Script Transform with the line of partial code as follows: Row.OutputName = StrConv(Row.InputName, VBStrConv.ProperCase)
Build dynamic SQL statement	Expression on the SQLStatementSource property of Execute SQL Task: "SELECT Column From " + @[User::TableName] +WHERE DateFilterColumn = '" + (DT_WSTR,4)YEAR(@[User::DateTimeVar]) + RIGHT("0" + (DT_WSTR,2)MONTH(@[User::DateTimeVar]),2) + RIGHT("0" + (DT_WSTR,2)DAY(@[User::DateTimeVar]),2) + "'" Expression Output:SELECT Column FROM MyTable WHERE DateFilerColumn = '20060915'
Calculate beginning of the previous month	Expression on component or task: (DT_DATE)(DT_DBDATE)DATEADD("dd",-1*(DAY(GETDATE())-1),DATEADD("month", -1, GETDATE()))
Round to the nearest two decimal mark	Expression on Derived Column Transform: ROUND(YourNumber, 2) Expression Output Example: 1.2600000