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

SSRS Expression Cheat Sheet

SSRS Expression Cheat Sheet
Problems	Expression
Return first day of current Week (ex. Default Start Date parameter to return WTD)	Expression on the parameter default value: =DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today) Expression Output Example: 11/7/2010 12:00:00 AM
Return first day of current Month (ex. Default Start Date parameter to return MTD)	Expression on the parameter default value: =DateAdd("d",-(Day(today)-1), Today) Or =DateSerial( year(today()), month(today()), 1) Expression Output Example: 11/1/2010 12:00:00 AM
Return first day of current year (ex. Default Start Date parameter to return YTD)	Expression on the parameter default value: =DateAdd("d",-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today) Expression Output Example: 1/1/2010 12:00:00 AM
Return period over period (ex. Default date parameters to a rolling year)	Expression on the parameter default value: Week over Week =DateAdd("ww",-1, Today) Month over Month =DateAdd("m",-1,Today) Year over Year =DateAdd("yyyy",-1, Today) Expression Output Example: 10/9/2010 12:00:00 AM
Return current month name	Expression in Text Box: =MonthName(Month(Today())) Expression Output Example: November
Uppercase fields	Expression in Text Box: =UCASE(Fields!FieldName.Value) Expression Output Example: NOVEMBER
Convert text to proper case (ex. 1st letter in each word is uppercase)	Expression in Text Box: =StrConv(Fields!FieldName.Value, VbStrConv.ProperCase)
Replace NULL with another value	Expression in Text Box: =iif(Fields!FieldName.Value = nothing, "No Value",Fields! FieldName.Value)
Alternating row color (Banding effect)	BackgroundColor property on Text Box: =iif(RowNumber(Nothing) Mod 2 = 0, "Silver", "White")
Handling division by zero	Expression in Text Box: =iif(Fields!DenominatorField.Value = 0, 0, Fields!NumeratorField.Value/ iif(Fields!DenominatorField.Value = 0, 1, Fields! DenominatorField.Value))
Remove a given character from a string (ex. Remove “-“ from a social security number)	Expression in Text Box: =Replace(Fields!EmailAddress.Value,"-","")

SSIS Data flow or T-SQL

Advantages of using Stored Procedures

• A SQL statement will outperform a SSIS data-flow when the data transform is table-to-table on the same server
• No new learning curve as ETL developers will already know how to write SQL code. SSIS data-flows are a completely new technology.
• Utilise transactions within the database rather than use MSDTC
• Easier to generate stored procedures from known metadata than it is with a data-flow (at the time of writing it is anyway)

Advantages of using a SSIS data-flow

• Handle data from heterogenous sources in the same place
• Consume data from sources that can't be accessed using a SQL statement
• Data can be transformed without the need for an intermediate staging area
• If data does need to be persisted temporarily it can be to a raw file whereas T-SQL requires temporary tables for which there may be a management/security overhead
• Extra transformation functionality (e.g. Fuzzy logic, data mining, text mining, insert to Analysis Services)
• Visual representation of the "work"
• Bad data can be captured to a different data sink for examination later
• Exception Handling
• Use .Net Data providers as an alternative to OLE DB Providers (e.g. mySAP Business Suite .Net Data Provider)
• Data-flows are, to some extent, self-documenting
• User-controlled parallel execution of data-flows is possible where it isn't really in the inherent batch operated world of stored procedures
• "Heavy-lifting" of data can occur on a different to machine to that storing the data (thanks to John in the comments section)
• By default, events containing very pertinent information such as "component "" (5824)" wrote 2398156 rows" are raised