SSIS Expressions – Date operations, Data Type conversions and more…

I had to use Derived Column Transformation Editor to split a datetime column into 2 parts one with Date only and another column only with Time. I had to invest some time to get the simple solution as follows…

--•	Removing Time part from Datetime or Convertign DateTime to Date only string
(DT_WSTR,30)(DT_DBDATE)[MyDateTime]
--•	Trimming only Time part of DateTime 
(DT_WSTR,8)(DT_DBTIME) [MyDateTime]
--•	Converting to Integer Data Type
(DT_UI4)[StringColumn]
--•	Replacing NULL value with empty string
REPLACENULL([myColumn],"")
--•	IF Else statement
[MyColumn]=="Value"?"TrueValue":"FalseValue"
--•	Replace IsActive value from Yes/No to 1/0, handle NULLs 
REPLACENULL(IsActive,"")=="Yes"?1:(REPLACENULL(IsActive,"")=="No"?0:NULL(DT_I2))
--
-- 

There are multiple blogs with useful information worth sharing:
• Integration Services Data Types and their description: http://msdn.microsoft.com/en-us/library/ms141036.aspx
• SSIS Expression Examples: http://social.technet.microsoft.com/wiki/contents/articles/3215.ssis-expression-examples.aspx
• MSDN Documentation for SSIS Expressions: http://msdn.microsoft.com/en-us/library/ms137547.aspx
• MSDN Documentation of all Functions: http://msdn.microsoft.com/en-us/library/ms141671.aspx
• All Legal cast operations allowed: Below image is fetched from https://msdn.microsoft.com/en-us/library/ms141704.aspx; on 2015/02/11
SSIS_Legal_Cast_operations

-Vijred

Advertisements
This entry was posted in Productivity, Referene, SQL, SSIS and tagged , , , , , . Bookmark the permalink.

One Response to SSIS Expressions – Date operations, Data Type conversions and more…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s