invalid data value from source table

Question

invalid data value from source table

Karma-343206

SSCrazy

Points: 2104
More actions
July 5, 2006 at 5:27 am

#67560

Hi,
I was wondering if anyone could shed some light on this problem.
I have an odbc datasource to a 3rd party transaction database. I am trying to import a particular table to SS2000 using a DTS package. The table has 50,000 rows. One field of type date contains ~3300 rows with the date 01/10/1899 the rest are 01/01/1900 there are no nulls.
The DTS package failed with this error;
TransformCopy 'DirectCopyXForm' conversion error: General conversion failure on column pair 21 (Source column 'poitem_chkdate' (DBTYPE_DBDATE), destination column 'poitem_chkdate' (DBTYPE_DBTIMESTAMP'))
Firstly i tried to change the destination field type from smalldatetime to datetime in case it was some form of predefined date range limit. That had no effect.
I then tried to create a specific tranformation for the field 'poitem_chkdate' and used a script to try and handle the tranformation and errors. It returned this error;
activex scripting tranform 'DTSTransformation__1' encountered an invalid data value for 'poitem_chkdate' source column.
I have looked at the table via the odbc in Access and can't find any dates other than the ones i've posted.
Could anyone explain this and how i might resolve it.
Thanks for your time
K.

Function Main()
dim srcDate

            srcDate = DTSSource("poitem_chkdate")

             if isDate(srcDate) then
                        if srcDate > #1 jan 1800# and srcDate < #1 jan 2050# Then
                                    DTSDestination("poitem_chkdate") = #31 dec 2073#
                        else
                                    DTSDestination("poitem_chkdate") = #1 jan 2074#
                        end if
            else
                        DTSDestination("poitem_chkdate") = #31 dec 2072#
            end if

            Main = DTSTransformStat_OK
End Function

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

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

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 1

Here's a thought, try truncing the date in it's native language and make sure the year part is formatted as YYYY to avoid the 2000 thing (year 30 is the default - but you can actually change this in the transformation properties).

Change your source to a query (if it's not already) and...

If the native sql is Access, use the format function like this:

SELECT format( test_dt, "mm/dd/yyyy")

FROM Table1

Or if it's Oracle,

SELECT TRUNC( test_dt)

FROM Table1

Or if it's SQL Server,

SELECT cast(convert(varchar(15), test_dt, 101) as smalldatetime)

FROM Table1

or whatever... you get the idea...

However those dates sound bogus - like they really should be null, so maybe now is a good time to do some data scrubbing?

[font="Courier New"]ZenDada[/font]

Karma-343206 SSCrazy Points: 2104 More actions · Answer 2

Hi,

Thanks for the ideas. Bizarrely i tried running an Access query against the table linked via the obdc and it couldn't find the 01/10/1899 records it's as if they don't exist, neither are they being returned as nulls. Your correct in assuming they are pseudo-nulls the company that provides the database use 01/01/1900 as a default for date fields but they are currently at a loss to explain the 01/10/1899 values.

I'll give your suggestions a go and see what happens.

Thanks

K.

Philip Davis Old Hand Points: 307 More actions · Answer 3

Hi

I have encountered similar problems with our ERP software when trying to extract 'date fields' via ODBC. I get similar messages implying that there is an invalid value in one of the rows. Our ERP supplier said that they had found something on the Microsoft site suggesting that this was fixed in SQL 2000 SP4, but we have been using SP4 for ages. Another quick point with regards to dates: we also have to be careful when using a 'zero' date, as SQL and Office both started counting from the same date, but one started at zero and the other started at one! Not sure if it helps to know you're not alone in your frustration?

Luke L SSC-Dedicated Points: 35547 More actions · Answer 4

I dont' know if this will help or not, but 01/01/1900 is what you get when you cast '' as a datetime. You get 01/10/1899 when you subtract 365 from '' AS in

cast('' as datetime) - 365

To help us help you read this[/url]For better help with performance problems please read this[/url]

Karma-343206 SSCrazy Points: 2104 More actions · Answer 5

Hi,

Thanks for everyones help and support

I've managed to work out a solution for the time being.

As i'm building a mirror of the transaction database i'm dropping the tables each night and rebuidling them then importing the data. So when i initially create the table i'm making the column a varchar, then i'm converting the dates which appear as 0000-00-00 to 1900-01-01 once they are in the SS table and then i'm changing the column type to datetime. There are a couple of tables with this issue and i'm endevouring to get the supplier to sort out the validation on their front-end and fix the data that is in their db but this will do for the time being.

Thanks again

K.