SSIS convert datetime from ISO format YYYY-MM-DDTHH:MM:SS.000Z

Question

I have a flat file with a date column. It is formatted with an ISO 8601 datetime standard. Format looks like this: 2013-10-28T10:23:01.000Z

How can I convert that into a datetimestamp format? I have used "derived columns" but I haven't found the type cast.

Answer 1

I created a sample package, added a Flat File Connection Manager and defined my two columns with data type of DT_I4 and DT_DATE

rownum,eventdate
1,2013-10-28T10:23:01.000Z

I then added a data flow with a Flat File Source, selected the source file and boom import failed. The import fails because of the most maddening error

Data conversion failed. The data conversion for column "eventdate" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

How can a date not be converted to date? The design decision was to be as friendly as possible with importing data. This means that people using sane, standards based, textual representation of things like datetime get hosed. Lazy people in the US though can use 01-02-05 and have their data interpreted as Jan 2, 2005 while the lazy gits in the UK can have their Feb 1, 2005 and Japanese folks think everyone's mad as it's clearly Feb 5, 2001.

So, locality is to blame as by default, it tries to out-think your source data. The solution then, is to tell SSIS to shut up and take my data already!

Shut up and take my data already!

In your data flow, right click on your data source and select the advanced editor. In the Input Output Properties tab, expand your Output Columns and for each column that contains this good data, you need to set the FastParse from its default of false to True