Data type conversion issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Data type conversion issue

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-25 : 08:12:04

I have a column with the following data:

9999-99-99
9999-99-99
01/01/2008
9999-99-99

SSIS appears to determine the datatype output by evaluating the first few lines of the column. When it decides the column is of DATE datatype, all the '9999-99-99' values are ommitted, and when it decides the datatype is STRING, all the dates are ommitted. To prove the point, create a spreadsheet with the following 2 columns and import to a table:

A B
9999-99-99 01/01/2008
9999-99-99 01/01/2008
9999-99-99 01/01/2008
01/01/2008 9999-99-99

A will be a string datatype
B will be a date datatype

I want to force the datatype. When I go into advanced properties of the excel source, and set column 'A' to output date[DT_DATE], it fails. If I change the error output to 'ignore truncation or error disposition', the values are NULL.

Any ideas of how I can get date values from column A and string values from column B???

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 08:19:09

Of course 9999-99-99 will always be a string since it is not a valid date.
I have never heard of month 99 nor day 99.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 08:20:21

9999-99-99 is not a valid date value in SQL Server. i think what you need to do is to convert them to maximum date value available in sql server which is 31-12-9999 and then store it in sql table. you can bring them as string from source, use a derived column task to change them to max date value and then use type conversion task to convert them to datetime.

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-25 : 08:25:42

Unfortunately, I have no control over the format of the date. I receive this as a spreadsheet obtained from an external source, so it is not possible to get this changed.

However, I have to use the spreadsheet as my source.

Any other ideas?

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 08:27:47

quote:
Originally posted by Bex

Unfortunately, I have no control over the format of the date. I receive this as a spreadsheet obtained from an external source, so it is not possible to get this changed.

However, I have to use the spreadsheet as my source.

Any other ideas?

Hearty head pats

did you try converting to vaild date value as i suggested?

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-25 : 08:34:46

Problem is, the data is not even being read. If you open up a data viewer (grid) on the connector between source and destination, you will see that the data is being read as NULL, therefore, I can't do any conversions, as I can't even get the data into a table.

If I could just do a simple dump into a table, then I could manipulate the data as you suggested. But so far, I'm failing at the first hurdle, and cannot even get the data into a table. Does that make sense?

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-25 : 08:43:15

So in conclusion, my ultimate aim is to get all of column A into a VARCHAR column. But the dates are being set to NULL.

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 08:45:37

quote:
Originally posted by Bex

Problem is, the data is not even being read. If you open up a data viewer (grid) on the connector between source and destination, you will see that the data is being read as NULL, therefore, I can't do any conversions, as I can't even get the data into a table.

If I could just do a simple dump into a table, then I could manipulate the data as you suggested. But so far, I'm failing at the first hurdle, and cannot even get the data into a table. Does that make sense?

Hearty head pats

what happens when you try to attach a derived column task to your excel source connection?

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-09-25 : 08:53:03

Same topic? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110571

X002548
Not Just a Number

15586 Posts

Posted - 2008-09-25 : 09:55:25

Load Excel to a staging table first...then do audits and data integrity checks to populate your base table

What, for example, does bozo source data person expect you to do with a monthh of 99?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-26 : 05:02:32

Sorry guys

It was the same topic as i had posted beforehand.

However, reading back on the posts, someone made a suggestion, which I didn't previously get, but now it makes sense.

As recommended, I changed the source connection to OLE DB rather than excel, and this worked fine, I could see both the date and string values.

Thanks for all your help

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources