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.
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
(DT_DATE)((SUBSTRING((DT_WSTR,8)IntegerDate,5,2) + “-” +
SUBSTRING((DT_WSTR,8)IntegerDate,7,2) + “-” +
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.’
(DT_DATE)((SUBSTRING(StringDate,6,2) + “-” + SUBSTRING(StringDate,9,2) + “-” +
CREATE TABLE Dates
IntegerDate INT NOT NULL,
StringDate CHAR(8) NOT
INSERT INTO Dates VALUES (20081231, ‘20081231’)
INTO Dates VALUES (20070101, ‘20070101’)
INSERT INTO Dates VALUES (20010130,
INSERT INTO Dates VALUES (20051232, ‘20051232’)
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.