OLEDBSource->FlatFileDestination: datetime problems 1

[LFCfan]

My OLEDBSource is a Sql Server table, which has some varchar columns. These columns all contain "dd-mm-yyyy hh:mm:ss" data, which is how I would like them exported to the flatfile.


The problem is that despite the table columns being varchar, the dataflow task (the green arrow between the two) picks up these fields as being database timestamp types, and my flatfile ends up with the date strings formatted as "yyyy-mm-dd hh:mm:ss", which I don't want.

I can't for the life of me see how to change this; you can only view rather than edit the column metadata if you edit the data flow path.

Has anyone else come accross this?

Many thanks



~LFCfan

 

[ousoonerjoe]

Take a look at the CONVERT keyword. You can control the format of DATETIME then if still needed, CAST the value as a VARCHAR.

--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"

"Don't stop him. He's roll'n."
--------------------------------------------------

 

[MDXer]

Even easier than the convert is:

Right Click on your OLE DB Source and select Advanced properties.

go to the last tab and expand the output node in the tree view. find your column in the list and change the data type to be a DT_String.

 

[LFCfan]

MDXer
Thanks, that did the trick. I hadn't spotted the advanced editor - couldn't they just have put an "Advanced" tab on the normal editor!

oosoonerjoe,
Thanks. I didn't try that option because CONVERT didn't seem to have an option for the exact formatting I needed. Also I suspect that, as the column being exported was already a varchar, it wouldn't have made a difference due the OLEDBSource set-up

~LFCfan