DTS Transfer with Excel as a datasource

[newcruser]

Currently, we are using DTS to basically perform a 3 step process:
1) Get data from Oracle and place it on a SQL Staging Database
2) Migrate the data from the Staging Database to the Reports Database (both residing on SQL Server)
3) Massage tables residing on the Reports Database to populate tables.

We are successful in both steps 1 and 2, but when we try to migrate data from one table to the other, we receive the error message: 'cannot alter a varchar to numeric' when it has already processed 41,000 records. Is this due to the fact that the DTS uses Excel as a datasource to perform loads and Excel can only handle up to 65,000 records? We want to peform a transfer of over 100,000 records.

Has anyone out there encountered this before?

 

[dbomrrsm]

you are trying to put varchar data into a numeric field which cant be done without cast/convert

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[newcruser]

This appears to work on our development server (contains the same DTS package settings) but we are only converting a few records over. Also, when we run the package, it stops at 41,000, so that makes me wonder if it is because of a bad data record, or a limit on the number of records Excel as a data source can hold.

Any ideas?

 

[dbomrrsm]

probably is a bad data record
to find do

select varcharfield
from sourcetable
where isnumeric(varcharfield) = 0

this will find all the varchar values that cant be converted to numeric

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Catadmin]

Like dbomrrsm said, it is a record that doesn't want to convert hiding around row 41,000 or so.

Use his code above, but do a Select * instead of Select varcharfield so that you can see the entire record. That way you have other criteria to look at the problem field in question so you understand why the error occurred in the first place.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!