<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=612681139262614&amp;ev=PageView&amp;noscript=1">
Skip to content

Need help? Talk to an expert: phone(904) 638-5743

SSIS Expressions Cheat Sheet

Problem 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 on the Flat File or File Connection Manager: 
 
"C:\\Project\\MyExtract" + (DT_WSTR, 30)(DT_DBDATE)GETDATE() + ".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