I've made a DTS-Package that first delete rows from a tabel and then put new rows in that table. This package runs by a batch-file (ISQL; dtsrun /s database /e /n Batchname).
Since a few weeks the batch runs with error's

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pum
Step Error Description:The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (8004202b): TransformCopy 'DTSTransformation__1' conversion error: Conversion invalid for datatypes on column pair 4 (source column 'FTE' (DBTYPE_VARNUMERIC), destination column 'FTE' (DBTYPE_R4)).
Step Error code: 8004206
Step Error Help File:sqldts80.hl
Step Error Help Context ID:

When I start the batch in the morning it sometimes runs with no problems en sometimes it fails.
Can someone help me

It looks as though you have bad data. What this error message is telling
you is your source data will not fit into the datatype at the destination.
Some of the data will be compatible but others not. You have two different
data types and therein lies the problem.

--

----------------------------

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


"Piet Boersen" <anonymous (AT) discussions (DOT) microsoft.com> wrote

database /e /n Batchname).
(8004202b): TransformCopy 'DTSTransformation__1' conversion error:
Conversion invalid for datatypes on column pair 4 (source column 'FTE'
(DBTYPE_VARNUMERIC), destination column 'FTE' (DBTYPE_R4)).)

Allan

Thanks for the reply

I also thought that bad records where the problems. But when this batch runs in the night, nobody is working and then the package goes always wrong. In the morning when I run the package from Enterprise manager it sometimes goes wrong. And when it goes wrong I retry immediately an then is goes good!
I've already checked of the database was online at night! (the data comes from an Oracle database).

Urgh!

If it always goes wrong overnight then maybe there is a localisation issue
with the SQL Server Agent account. try logging in as the service (SQL
Server Agent) account and see if it always fails for you.

--

----------------------------

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


"Piet Boersen" <anonymous (AT) discussions (DOT) microsoft.com> wrote

In the morning when I run the package from Enterprise manager it sometimes
goes wrong. And when it goes wrong I retry immediately an then is goes good!

I'll try for tonight.

The solution did not end the problem!!

Then you need to look at the data in isolation. Have the "to be imported"
data moved to another table and do a comparison of the attributes that are
going wrong



--

----------------------------

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

Futher researce gives the following facts

The DTS-Package is called in a batch-file by DTSRUN. When I run the batch-file in the DOS-prompt then the DTS-Packages runs good. When I start the DTS-Package by the Windows scheduler then the DTS Package goes wrong!
In de Windows scheduler I run the package by the same user as on the DOS-prompt!