Having trouble getting a Date?

There have been several questions posted lately on the MSDN SSIS Forums asking how to convert integer or string date values into "real" DATETIME values in an SSIS package. I figured that this would already be over-documented online, but as the first result I found online (from Kirk Haselden, no less!) didn't actually work, I thought I might as well blog on this myself.

So how do you do this? The key [1] is using the Derived Column transformation in your package data flow, and using the correct expression. Your package's data flow should look something like this [2]:

What's not obvious from the image is what the Derived Column transform is actually doing. This depends, of course, on the input value. Here's the first question from the forums:

My source database stores dates as integers (e.g. 20070101). I need to
convert to a "real" date for my target system.

I'm guessing I need
to create a derived column - could someone help me out with the appropriate
expression?

In this scenario, you must first convert the integer "date" to a string, and then parse that string to get it into the correct format, and then cast that string into a date. Here's the expression:

(DT_DATE)((SUBSTRING((DT_WSTR,8)IntegerDate,5,2) + "-" +
SUBSTRING((DT_WSTR,8)IntegerDate,7,2) + "-" +
SUBSTRING((DT_WSTR,8)IntegerDate,1,4)))

The next question from the forums was this:

Is it possible to convert a date stored as a string into a datetime with
integration services 2005? My attempts with the “data conversion” fail. The
string type form of the date is ‘yyyy-mm-dd’ and the desired result for use in a
Union All is ‘dd/mm/yyyy 12:00:00AM.’

The only thing different between this problem and the one before is in the expression required to perform the conversion. In this case, since we have a string input already, the expression is a little simpler:

(DT_DATE)((SUBSTRING(StringDate,6,2) + "-" + SUBSTRING(StringDate,9,2) + "-" +
SUBSTRING(StringDate,1,4)))

To test these expressions, I created a few simple tables and populated them with sample data. Each test scenario got three valid date values, and one value that is valid for the underlying storage type (integer or string) but not a valid date. Here are the SQL Scripts:

CREATE TABLE Dates
(
IntegerDate INT NOT NULL,
StringDate CHAR(8) NOT
NULL
)
GO
INSERT INTO Dates VALUES (20081231, '20081231')
INSERT
INTO Dates VALUES (20070101, '20070101')
INSERT INTO Dates VALUES (20010130,
'20010130')
INSERT INTO Dates VALUES (20051232, '20051232')

With this as the source, we end up with results like this when we run the package:

[3]

It's vitally important to have both a success path and an error path coming from your Derived Column transform. There is no domain integrity in the source column, so there is no way to be certain that the values you're getting from the source can be successfully converted into dates. Unless you set up your package like this, the first time you get a bad date, your package will fail, instead of handling the error gracefully.

[1] Actually, the key is always using the best tool for the job. If you're getting your data from a relational database, you should probably just do the conversion there using CAST or CONVERT. This will make for a simpler package and usually better performance as well.

[2] Although in the real world, you may want to actually write your records to some destination instead of just counting the rows.

[3] When was the last time a developer got four dates and three of them were good and only one of them was bad? Perhaps I should have come up with a more realistic scenario, eh?