Using Derived Column to Convert String to DateTimeStamp

[elinsd]

I have a flat file trailer row that looks like this: T|3|2007120713:39:59

I'm able to extract the row using conditional split but I want to convert the datatime string into datatimestamp so I can insert it into a table. I built the derived column expression as following but got errors such as "The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis." I've been looking at this for hours with no luck. And is there other alternative to convert this column? Any help would be grately appreciated!!

(DT_DBTIMESTAMP)(SUBSTRING(Line,FINDSTRING(Line,"|",2) + 1,4)+"-"+SUBSTRING(Line,FINDSTRING(Line,"|",2) + 5,2)+"-"+SUBSTRING(Line,FINDSTRING(Line,"|",2)7,2)
+" "+SUBSTRING(Line,FINDSTRING(Line,"|",2) + 9,8))

 

[MDXer]

Try out putting this as a text column and then place a dataviewer on the dataflow and visually see what your string looks like.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[MDXer]

also try appending .000 to the end of the Time piece it may be that the DT_DBTimeStamp expects a compete DateTime.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[elinsd]

Thanks for responding to my question.

I finally got it. The systax was fine but I got unexpected carriage returns in the middle of the string so it failed. After re-typing the whole expression again, it worked.

 

[MDXer]

Notepad or some other text editor is what I use to troubleshoot my expressions. Their editing windows for expressions suck.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck