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.
| Author |
Topic |
|
smorty44
Yak Posting Veteran
93 Posts |
 Posted - 2008-03-10 : 15:29:04
|
| I'm trying to set up a DTS Package to import data from Adaptive Server Anywhere 9.0 into SQL 2000 and am getting the error "Insert Error, column 9('D_PO_END_DATE',DBTYPE_DBTIMESTAMP),status 6: Data overflow. Invalid character value for cast specification." due to a couple of bad dates, "9999-12-31", "0400-06-01" going into a smalldatetime field. I'm just learning DTS, how do I convert the bad dates to null before they get imported into SQL? |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-10 : 16:51:13
|
YOu would alter the activex script for the copy of that column.When you go to edit the transformations you can either change the existing activex script if it is all done in there, or if they are all done individually (each column has it's only line to the destination) you can pick THAT one and replace it with an activex script instead of the default copy column taskIn the activex script you would do something like:IF isDate(DTSSource("col009")) =1 then DTSDestination("datetimecolumnnamehere") = DTSSource("col009") instead of this which is how the wizard would do it when you choose the activex option.DTSDestination("datetimecolumnnamehere") = DTSSource("col009")
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
smorty44
Yak Posting Veteran
93 Posts |
Posted - 2008-03-10 : 17:25:44
|
| Thank you for the info dataguru, however, this returns the entire column as null values and I know there are valid dates in these columns. Is this because it's coming from Adaptive Server Anywhere and maybe the dates are not in a recognized format? If I bring this table in with the problem field as varchar I can see valid dates example, 2007-08-16. Is there another function in activex script I should use? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-10 : 17:53:22
|
sorry, just use isDate() without the = 1IF isDate(DTSSource("col009")) then DTSDestination("datetimecolumnnamehere") = DTSSource("col009") was mixing languages. one of the systems I code in requires an explicit boolean result instead of an implied boolean.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
smorty44
Yak Posting Veteran
93 Posts |
Posted - 2008-03-11 : 09:38:10
|
| Ok, this is working in most cases. I have another table giving me problems due to a value of "5/30/606". The isdate formula must not catch this as a bad date so I'm getting errors trying to insert into a datetime field. I'm trying to keep this design data driven and not use a specific data entry error to write the function. Using < 1-1-1900 does not work nor > 12-31-9999. Any suggestions? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-11 : 10:03:20
|
If you use the isDate() function as above on the column it would return false for that date..not sure why it is not catching it. The only time I had this happen I had a different column name in the isDate() than required.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
|
|
|
|
DTS Data overflow error