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
|
Common SSIS Problems and Solutions
|
| Problems
|
Solutions
|
| Loop over a list of files & load each one
|
Tasks Required: Foreach Loop, Data Flow Task
Solution: Configure the Foreach Loop to loop over any particular directory of files. The loop should be configured to output
to a given variable. Map the given variable to a connection manager by using expressions.
|
| Conditionally executing tasks
|
Solution: Double-click the precedence constraint and set the Evaluation property to Expression and Constraint.
Type the condition that you want to evaluate in the Expression box.
|
| Pass in variables when scheduling or running a package
|
Solution: Use the /SET command in the DTExec command line or change the Property tab in the Package Execution Utility to have the property path like:
\Package.Variables[User::VariableName].Properties[Value]
|
| Move and rename the file at the same time
|
Tasks Required: File System Task
Solution: Set the File System task to rename the file and point to the directory you'd like to move the file to. This enables you to rename and move the file in the same step.
|
| Loop over an array of data in a table & perform a set of tasks for each row
|
Tasks Required: Execute SQL Task, Foreach Loop
Solution: Use an Execute SQL Task to load the array and send the data into an object variable. Loop over the variable in a Foreach Loop by use an ADO Enumerator.
|
| Perform an incremental load of data
|
Tasks Required: 2 Execute SQL Tasks, Data Flow Task
Solution: Have the 1st Execute SQL Task retrieve a date from a control table of when the target table was last loaded and place that into a variable.
In the Data Flow Task, create a date range on your query using the variable. Then, update the control table using a 2nd Execute SQL Task to specify when the table was
last updated.
|
| Perform a conditional update & insert
|
Components Required: Data Flow Task, Conditional Split, Lookup Transform or Merge Join, OLE DB Command Transform
Solution: Use the lookup Transform or Merge Join to detemine if the row exists on the destination and ignore a failed match. If the row yields blank
on the key, then you know the row should be inserted into the target (by Conditional Split). Otherwise, the row is a duplicate or an update. Determine if the row
is an update by comparing the source value to the target value in the Conditional Split. The update can be done by an OLE DB Command Transform or by loading the data
into a staging table.
|