Date Conversion Problems Using BIDS

Question

Date Conversion Problems Using BIDS

bkmooney

SSCrazy

Points: 2041
More actions
February 27, 2014 at 11:44 am

#290364

Hi,
I'm loading a bunch of files that have one column which is a date/time stamp. It's of the form: "2/27/2014 13:30".
I load it as a string and then use an if statement to generate a derived column where any missing values are replaced with a NULL string. That all seems to be working fine...
Now I need to use a data conversion to get the string into a date/time format that can be loaded into my database. The column type is (datetime, NULL) in the database.
For some reason, whatever data type I select in the data conversion editor throws an error that data will be lost and fails. The types I've tried include:
database timestamp [DT_DBTIMESTAMP]
database timestamp with precision [DT_DBTIMESTAMP2]
database date [DT_DBDATE]
date [DT_DATE]
What am I missing that is making this fail? And really, it fails before it even tries to get to the stage of writing out to the database. I feel like I'm missing something totally obvious.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

What's the data type for the column on the connection?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

bkmooney SSCrazy Points: 2041 More actions · Answer 2

It comes in from a flat file and is read in as a string [DT_STR] with length 50.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I created a file that includes exactly this:

"2/27/2014 13:30"

My test connection has Text qualifier: "

And it previes like this:

2/27/2014 13:30

I added the Derived Column with the following expression:

(DT_DBTIME)DateTest

And inserted into a datetime column.

It runs without a problem. Do you have something different from this?

EDIT: DateTest is the name I gave to the source column (from the flat file)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Koen Verbeeck SSC Guru Points: 259125 More actions · Answer 4

What is your locale?

If "2/27/2014 13:30" is interpreted like "dd/mm/yyyy", you have a problem.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

bkmooney SSCrazy Points: 2041 More actions · Answer 5

How would I determine my locale settings? I believe it should interpret it as I intended, but want to double check.

Thanks!

Koen Verbeeck SSC Guru Points: 259125 More actions · Answer 6

In the package properties, you have LocaleID.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP