DTS Problem

I have a DTS package which copies rows from 150 tables using Union ALL.
Around 30 million rows are involved. However package fails after 8.6 million
rows with the error:
Error at Source for Row number 8697091. Cannot convert nvarchar value of
('XXX ') to smallint. Errors encountered so far in this task: 1.
The column on which this fails is a Varchar. It looks like the DTS package
is treating the source package as a Smallint. Is there a way aroun this?I would have thought it is trying to convert a varchar (source value)
to a smallint (destination value) '|||I don't think that is the case. My Source (SQL query) column has values like
2, 'XXX' and the column in the destination table is defined to be varchar.
The Source tables have this column defn as smallint and varchar.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1127930452.777681.80430@.g43g2000cwa.googlegroups.com...
>I would have thought it is trying to convert a varchar (source value)
> to a smallint (destination value) '
>|||How do you have a column as Smallint & Varchar ?|||This is one time data transfer and this is my staging table. I am bringing
data from various tables which may be int or varchar.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1127930934.509640.143400@.z14g2000cwz.googlegroups.com...
> How do you have a column as Smallint & Varchar ?
>|||Hmm strange...
Maybe you could replace 'XXX' with zero in your Select statement.
I know if you extract Data to a text file you skip the exceptions and
write them to an exceptions file.
If you used this approach you could have several txt files, which would
be from your source tables, and then import these in to new table.
Sorry I can't be of any more help
Barry|||I have tried exporting this to a text file, but same issue.
Anyone? All I need I think is a way to tell the DTS that the source column
is varchar.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1127931933.066339.209980@.g49g2000cwa.googlegroups.com...
> Hmm strange...
> Maybe you could replace 'XXX' with zero in your Select statement.
> I know if you extract Data to a text file you skip the exceptions and
> write them to an exceptions file.
> If you used this approach you could have several txt files, which would
> be from your source tables, and then import these in to new table.
> Sorry I can't be of any more help
> Barry
>|||You need to specifically convert the value to varchar for the column that
has integer value.
try this:
select '0e'
Union
Select 1
It will give you the error mesage.
But this one won't:
select '0e'
Union
Select Convert(varchar(5), 1).
Perayu
"XXX" <sa@.nomail.com> wrote in message
news:%23Ji7dqFxFHA.2008@.TK2MSFTNGP10.phx.gbl...
>I have tried exporting this to a text file, but same issue.
> Anyone? All I need I think is a way to tell the DTS that the source column
> is varchar.
> "Barry" <barry.oconnor@.singers.co.im> wrote in message
> news:1127931933.066339.209980@.g49g2000cwa.googlegroups.com...
>