Purchase On-Demand SSIS Training

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 Transfrom 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) + "'"                        
                        
                    
Round to the nearest two decimal mark

Expression on Derived Column Transform:

                        
ROUND(YourNumber, 2)

Expression Output Example: 1.2600000