I have a SQL Server 2k DTS Package which is missing/ignoring rows when they
are transferred between tables. The source is a Query covering 15 tables and
the destination is a single table. When I run the Query in Query Analyzer I
get 87000 rows return yet when I run it as part of a DTS task the destination
table only has 81700 or so rows.

Sorry forgot to add both source and destination tables are MS Sql Servers
Running 2K with SP3a

"andrewharris" wrote:

Is there anyhing special about the rows missing.
Are both table EXACTLY the same(indexes, datatypes, constraints etc etc)

Set the package to fail on first error

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"andrewharris" <andrewharris (AT) discussions (DOT) microsoft.com> wrote

There's nothing special about the row's, i.e. it will take 2 out of 3 lines
from a sales order. The indexes are diferent and so are some of the data
type's i.e. replaced chars with varchar's but the rest are the same. It ius
set to fail on the first error, but dts isn't giving any errors, which is
what is confusing.

Andrew

"Allan Mitchell" wrote:

Can you identify the rows?
Can you look at them in a hex editor and see if they are terminated correctly?

What happens if you tried to enter that row manually?

What about taking the offending rows out, put them into their own text files?
Still fail to insert?
What about the main file? All Successful?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"andrewharris" <andrewharris (AT) discussions (DOT) microsoft.com> wrote

Not very easliy (have to do a manual check)
Not sure how I would use a hex editor to check the contents of a SQL
Database or where to start looking.

I've not tried to enter them manually, but I supect there wouldn't be a
problem.

I don't have the luxery of being able to remove the offending rows as the
source system is our main MIS package.

"Allan Mitchell" wrote:

Sorry I got carried away with the source being a text file for some reason.

Forget DTS. Can you do this using a plain old fashioned INSERT?

When you issue the SELECT in the Source using QA can you see the rows you want then?


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"andrewharris" <andrewharris (AT) discussions (DOT) microsoft.com> wrote

No worries.

As I'm not during any transformation just moving the data I couild re-write
it as a select. But it will be be and ugly.

Yeah in QA you can see the missing row's are they appear to be no differnet
to row's before and after.

I've deceide to bite the bullet and raise it with MS, I'll post back if I
get an answer to the problem from them, thnaks for the help.

Andrew



"Allan Mitchell" wrote:

OK sounds interesting if the rows are not special.

Can you isolate those rows in the select and move only them?



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"andrewharris" <andrewharris (AT) discussions (DOT) microsoft.com> wrote

Don't believe I can do it directly but might be able to put in a second dts
step which combine's again selects from the source db but only returns row's
which aren't in the dest table. i'll give it a go and see what happens.

"Allan Mitchell" wrote: