T-SQL Tuesday #001: A Couple SSIS Date Expressions

andyleonard — Tue, 08 Dec 2009 16:26:00 GMT

Adam Machanic is hosting this month's T-SQL Tuesday (the first!) on Date/Time Tricks. I thought I'd share a couple datetime SSIS Expressions I use regularly to format dates.

First, I created an SSIS solution named ExpressionsTester, to which I added a data flow transformation, to which I added an OLEDB Source Adapter, Derived Column Transformation, and Trash Destination Adapter. I also created a Connection Manager aimed at AdventureWorks. I don't use the Connection Manager for anything - I simply need a connection manager to configure the OLEDB Source Adapter (someone needs to write a Trash Connection Manager...).

When configured, it looks like this:

The OLEDB Source (named "Now Source") is configured as shown:

The query is:

Select getdate() as Now
-- Select '1/1/1972' as Now

Inside the Derived Colum Transformation (named Derive DateTime Stuff) I create a couple columns based on the value of the Now column supplied by the Now Source source adapter. They are named StrDateOnly and StrLeadingZeroDateOnly:

The expression for StrDateOnly is:

(DT_STR,10,1252)((DT_STR,2,1252)MONTH(Now) + "/" + (DT_STR,2,1252)DAY(Now) + "/" + (DT_STR,4,1252)YEAR(Now))

The expression for StrLeadingZeroDateOnly is:

(DT_STR,10,1252)(((MONTH(Now) < 10 ? "0" + (DT_STR,1,1252)MONTH(Now) : (DT_Str,2,1252)MONTH(Now))) + "/" + ((DAY(Now) < 10 ? "0" + (DT_STR,1,1252)DAY(Now) : (DT_Str,2,1252)DAY(Now))) + "/" + (DT_Str,4,1252)YEAR(Now))

Executing today (12/8/2009) produces the following output:

Note that both outputs are of the String data type (DT_STR in SSIS). This translates to the varchar data type in SQL Server, but can be cast / converted as needed. Also note the StrLeadingZeroDateOnly places a leading zero before the day (08) while StrDateOnly does not - and that's the only real difference between the two expressions.

I can hear you thinking: "Andy, why didn't you simple convert this in T-SQL?" I'm glad you asked! In this example, my source happens to be an OLEDB adapter, ostensibly connected to a relational database. This is not always the case. In SSIS, we regularly load data from flat files, XML, or custom sources that don't have a handy (or simple) way to coerce data types.

:{> Andy

Search results matching tags 'SSIS', 'T-SQL Tuesday', and 'Expression Language'

T-SQL Tuesday #001: A Couple SSIS Date Expressions