CODEWITHSUNDEEP
sql-server-2008ssis
Haeflinger
Haeflinger

Reputation: 465

Import String Date in Derived column

I have a string date being read in from a file in the following format and is giving me cast error.

Oct 25 2011 10:18:10:756PM

I am using Derived Column to cast the string to a date.

I have tried the following in the expression with no success

(DT_DBTIMESTAMP)myDate
CAST(myDate as datetime)
CONVERT(datetime, myDate)  //I get parse error on this.

there has to be an easy way to cast this. Thanks in advance.

Upvotes: 2

Views: 2719

Answers (1)

billinkc
billinkc

Reputation: 61211

Detailed answer but I beg to differ as to whether "SSIS cannot recognize the date format provided in the question."

Perhaps if it was restated as "SSIS cannot recognize the supplied date format without help." The root issue in this case, is that by default the date and number parsing routines are locale aware. In general, this a good thing, except when it's not. I first stumbled over this when I was dealing with dates in a format of ccyymmdd coming off a mainframe. As others indicated, it'll parse in tsql, why not SSIS? There are plenty of articles out there advocating slicing and dicing the string data to make it into a valid datetime but why go through all that hassle?

Given this as sample input data (tab delimited). 

LongDateDesiresFastParse    Gibberish
Oct 25 2011 10:18:10:756PM  Hello world
Oct 24 2010 10:18:10:756PM  Hello 2010 world
Oct 23 2009 10:18:10:756PM  Hello 2009 world
Oct 22 2008 10:18:10:756PM  Hello 2008 world

And a package that looks like this,

Control flow

By changing one setting on the Flat File Source, I can make the package fail or not.

Generic data flow

Right click on the Flat File Source and select "Show Advanced Editor." On the "Input and Output Properties" tab, expand the Output Columns and find the column containing the date. Change the FastParse setting from False to True.

Setting fast parse in a flat file source

When I ran it, the package originally failed as it was losing precision storing that value into a DB_TIMESTAMP. I was successful when I set the column to type DB_TIMESTAMP2

Connection Manager, advanced tab

Demo package available at https://sites.google.com/site/billfellows/home/files/FastParse.dtsx?attredirects=0&d=1

Upvotes: 3

Related Questions