AdSense

Tuesday, 24 September 2013

Lifesaver - SSIS Quick fixes

Lifesaver - SSIS Quick fixes
How many times have you thought in development that "This must be a common scenario" and then searched all over the place to find the solution for it. I am listing down such scenarios and fixes for those scenarios which are very frequent in the software industry. This will help not only to jumpstart but save your time afterwards durng development. I will be trying to cover as many areas as I can under the heading "Lifesaver".
Create a file name with today's date

Expression on Flat File or File Connection Manager:
"C:\\SSISProject\\MySSISExport" + (DT_WSTR, 30)(DT_DBDATE)GETDATE() + ".csv"
Output: C:\SSISProject\MySSISExport2013-07-24.csv

Use a 2 digit date (ex. "07" for March instead of "7")

RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()),2)
Expression Output: 03 (if the month is March)

Multiple condition if statement

Say, we have a requirement where the a statement is needs to determine whether a column name is blank/NULL. If yes then should 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

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

Replace blanks with NULL values

Derived Column Transform in the Data Flow:
TRIM(ColumnName)=="" ? (DT_STR, 4, 1252)NULL(DT_STR, 4, 1252) : ColumnName

Convert text to InitCap,i.e, 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