Scribd
Upload
436 views2 pages

SSIS Expression Cheat Sheet

The document provides expressions and code snippets for common data transformation and manipulation tasks in SQL Server Integration Services (SSIS), including: 1) Creating a filename with today's date, extracting the first five characters of a zip code, uppercase data, replacing NULLs or blanks with other values, and removing non-numeric characters from a column. 2) Using logical AND and OR conditions in an if statement, replacing a character in a string, and converting text to proper case. 3) Building a dynamic SQL statement and rounding numbers to a specified number of decimal places.

Uploaded by

anon_142040477
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
436 views2 pages

SSIS Expression Cheat Sheet

The document provides expressions and code snippets for common data transformation and manipulation tasks in SQL Server Integration Services (SSIS), including: 1) Creating a filename with today's date, extracting the first five characters of a zip code, uppercase data, replacing NULLs or blanks with other values, and removing non-numeric characters from a column. 2) Using logical AND and OR conditions in an if statement, replacing a character in a string, and converting text to proper case. 3) Building a dynamic SQL statement and rounding numbers to a specified number of decimal places.

Uploaded by

anon_142040477
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

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 i
f 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 nu
mber)
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

You might also like