I am using DTS to transfer data from an ODBC database to SQL server on a
daily basis - all my transfers are working OK except for one. The
problem with this one is that one of the input fields is a text field
containing a number and the SQL server field is an int field.

Among other thing I have tried using Clng in the data transformation but
nothing I try works I get a type mismatch when the DTS runs.

How can I correct this?

Chris
North Cotswold Computing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

A text field? Is that text as in character or TEXT as in the SQL Server
datatype?

Is all the data in that field Numeric ? Because it is a character based
field it is possible that not everything is.


--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Chris" <anonymous (AT) devdex (DOT) com> wrote

The field type in the ODBC table is Text(10)

The value in the field is numeric - I am using a WHERE clause to check
that no other records are passed.

I enclose below my select code in case I have got it incorrect.

select
'TRANSACTION_TYPE','ACCOUNT_NUMBER','REFERENCE_NUM BER','TRANSACTION_DATE
','HISTORY_DEPOT_CODE','UNIT_PRICE','DELIVERY_ADDR _CODE','QUANTITY','EXC
HANGE_RATE','CURRENCY','ITEM_DISCOUNT_PCENT','ANAL YSIS','VALUE_AT_BUYING
_PRICE','HISTORY_SPARE_NUMBER','HISTORY_BIN_NUMBER ','POSTED_DATE','THIS_
RECORD','PARENT_RECORD'
from 'ACCOUNTING_SYSTEM'.'STOCK_HISTORY'
where 'STOCK_HISTORY'.'REFERENCE_NUMBER' > '0' AND
'STOCK_HISTORY'.'REFERENCE_NUMBER' < '999999999'

Chris
North Cotswold Computing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

And it is 'STOCK_HISTORY'.'REFERENCE_NUMBER' that is the Type Mismatch row
? You do not mention the field that is mentioned in the error.

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Chris" <anonymous (AT) devdex (DOT) com> wrote

Allan

Just to give you the background to this problem. The system started out
three years ago, the data is transfered into an Access database each
night and then during the day it is used to produce various reports.

The data in some of the tables is now > 1.5m records hence the reason to
migrate it to SQL server.

I know that the reference_number field only contains a number and the
ODBC routine has worked each night for the last three years. I have also
checked the data with a query just in case.


Chris
North Cotswold Computing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Yes you are correct it is the reference_number field that I am having
the problem with.

Chris
North Cotswold Computing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

If it has worked for the past 3 years then what has changed ?

1. Bad Data (Non-Numeric)?
2. Datatypes (This would more than likely be an overflow not a mismatch.

Are you using SQL Server 2000?

On the last table of the datapump you can choose to export the bad rows to a
text file. Try this and let's see what we get.

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Chris" <anonymous (AT) devdex (DOT) com> wrote

If it helps I have coppied below the transformation code

Function Main()
DTSDestination("THIS_RECORD") = DTSSource("THIS_RECORD")
DTSDestination("PARENT_RECORD") = DTSSource("PARENT_RECORD")
DTSDestination("ACCOUNT_NUMBER") = DTSSource("ACCOUNT_NUMBER")
DTSDestination("ANALYSIS") = DTSSource("ANALYSIS")
DTSDestination("CURRENCY") = DTSSource("CURRENCY")
DTSDestination("DELIVERY_ADDR_CODE") = DTSSource("DELIVERY_ADDR_CODE")
DTSDestination("EXCHANGE_RATE") = DTSSource("EXCHANGE_RATE")
DTSDestination("HISTORY_BIN_NUMBER") = DTSSource("HISTORY_BIN_NUMBER")
DTSDestination("HISTORY_DEPOT_CODE") = DTSSource("HISTORY_DEPOT_CODE")
DTSDestination("HISTORY_SPARE_NUMBER") =
DTSSource("HISTORY_SPARE_NUMBER")
DTSDestination("ITEM_DISCOUNT_PCENT") =
DTSSource("ITEM_DISCOUNT_PCENT")
DTSDestination("POSTED_DATE") = DTSSource("POSTED_DATE")
DTSDestination("QUANTITY") = DTSSource("QUANTITY")
DTSDestination("REFERENCE_NUMBER") = CLng(
DTSSource("REFERENCE_NUMBER"))
DTSDestination("TRANSACTION_TYPE") = DTSSource("TRANSACTION_TYPE")
DTSDestination("TRANSACTION_DATE") = DTSSource("TRANSACTION_DATE")
DTSDestination("UNIT_PRICE") = DTSSource("UNIT_PRICE")
DTSDestination("VALUE_AT_BUYING_PRICE") =
DTSSource("VALUE_AT_BUYING_PRICE")
Main = DTSTransformStat_OK
End Function


Chris
North Cotswold Computing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Allen

If it has worked for the past 3 years then what has changed ?

<1. Bad Data (Non-Numeric)?> All data in numeric
<2. Datatypes (This would more than likely be an overflow not a
mismatch.> Don't know

<Are you using SQL Server 2000?> yes I am

<On the last table of the datapump you can choose to export the bad rows
to a
text file. Try this and let's see what we get.> will try this and let
you know


Chris
North Cotswold Computing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I have found the problem - it is the where clause

With the JET routine only numeric records are selected
with the DTS where (same sintax) records such as 01/005 are allowed
through.

Have you any suggestions on how I can limit it to only the true numeric
records.

Thanks

Chris
North Cotswold Computing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!