I have a problem with a DTS package of Sql Server 2000 (Standard Edition).

The package has to import some record from one table to another one and flag
them with a X through a lookup query.
The problem is that sometime DataPump seems to skip some rows, that are not
exported but however flagged with X.
If I manually remove the flag from the table then the row was exported.....

Where is the problem?

Thanks
Daniele

P.S
Sorry for my english.....

So you export rows from one SQL Server table to another (same DB ?) and
based on some lookup value you flag them with an "X". Can we have a lok at
the Source Structure? The destination? Some sample data? What is the
Source Query for your datapump? Does it always skip the same rows?

--

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


"Nonno Oreste" <dffdfd (AT) dfd (DOT) dfd> wrote

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> ha scritto nel messaggio
news:enWfSejvDHA.2148 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
3.11


------Row transform function
Function Main()
DTSDestination("partita") = DTSSource("partita")
DTSDestination("numpro") = DTSSource("numpro")
DTSDestination("numset") = DTSSource("numset")
DTSDestination("esercz") = DTSSource("esercz")

Main = DTSTransformStat_OK
End Function

---------On insert success
Function updFlagg()
DTSLookups("udpFlgagg").Execute DTSSource("esercz"),
DTSSource("numset"), DTSSource("numpro"), DTSSource("partita")
updFlagg = DTSTransformStat_OK
End Function

------ DTS lookup query.....
UPDATE Pesate
SET flgagg = 'X'
WHERE (esercz = ?) AND (numset = ?) AND (numpro = ?) AND (partita = ?)

--------DataPump source
select *
from pesate
where flgagg <> 'X' or flgagg is NULL

------- Source table
CREATE TABLE [dbo].[Pesate] (
[esercz] [int] NOT NULL ,
[numset] [int] NOT NULL ,
[numpro] [int] NOT NULL ,
[partita] [int] NOT NULL ,
[classe] [int] NULL ,
[peso] [float] NULL ,
[flgagg] [char] (1) NULL ,
[Orario] [datetime] NULL ,
[Lotto] [nvarchar] (10) NULL ,
[Data] [datetime] NULL ,
[Toelet] [nvarchar] (1) NULL ,
[Ritoel] [nvarchar] (10) NULL
) ON [PRIMARY]

------Destination Table
CREATE TABLE [dbo].[fsuipe] (
[esercz] [decimal] NOT NULL ,
[numset] [decimal] NOT NULL ,
[numpro] [decimal] NOT NULL ,
[partita] [decimal] NOT NULL ,
[classe] [decimal] NULL ,
[peso] [decimal] NULL ,
[flgagg] [varchar] (2) NULL ,
[Orapeso] [varchar](8) NULL ,
[Lotto] [nvarchar] (10) NULL ,
[Dtpeso] [datetime] NULL ,
[Toelet] [varchar] (1) NULL ,
[Ritoel] [varchar] (1) NULL
) ON [PRIMARY]




time.
In general it happens in the morning from 6.00 to 7.00 and in the afternoon
after 17.00....
Unfortunately, in this period sql server does nothing of unusual.....

Now i have a doubt, can datapump manage a row-by-row process, or it was
implemented only for big data transfer?

DTS can do both row by row and BULK.

Make sure this query

select *
from pesate
where flgagg <> 'X' or flgagg is NULL


returns all the rows you think it does

--

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


"Nonno Oreste" <dffdfd (AT) dfd (DOT) dfd> wrote

I'm sure, because new row in source table have flag set Null at
default......
You have also to consider that while the dts work, new records can be
inserted in the source table....
In the sorce table are inserted about 6 records for minute......
The strange thing is that the task execution seems to be succesfull.... I
try to log the execution of the datapump in the exception file but no error
message are recorded....

Then move the records from the live table to a load table at the start of
the package and use that as the source instead. This way you don't care
about records being inserted into the live version. For your update
statement you don't do it anymore in the datapump. You join the load table
back to the live table on the PK val and uodate where they match. You then
clear the load table

--

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


"Nonno Oreste" <dffdfd (AT) dfd (DOT) dfd> wrote

Thanks Allan for your help, you're very kind.
I'll try your solution and then I let you know how it works....

But can you explain me where is the bug in the solution that I devolope?

What I think you were seeing is that when you initially read the data that
matches your criteria you read it into a rowset. This cannot be changed and
does not reflect new rows being entered into the table that match your
criteria. What you will then see at the end of the datapump is that there
may be rows that meet the criteria which were not updated leading us to
believe that the updating did not work when it actually did.

--

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


"Nonno Oreste" <dffdfd (AT) dfd (DOT) dfd> wrote

Hi Allan,
I've tried your solution. There is some problem.
Sometimes the last record that i copy are not flagged with 'X' and so the
next run DTS give me an error of duplicate key.....
The Dts are strucured :
1 - I create a temporary table with the non-flagged records
2 - I execute the DataPump to transfer data
3 - I execute the update query:
UPDATE Pesate
SET flgagg = 'X'
FROM
Pesate INNER JOIN Pesate_temp
ON Pesate.esercz = Pesate_temp.esercz
AND Pesate.numset = Pesate_temp.numset AND
Pesate.numpro = Pesate_temp.numpro
AND Pesate.partita = Pesate_temp.partita


Do you have some idea?

Your procedure should be

1. Take current qualifying rows to Pesate_temp
2. Set Pesate_temp as the SourceObjectName for your DataPump
3. ExecuteSQL task to do the updating of the real Pesate *
4. Clear Pesate_temp

*
From your UPDATE statement it would seem you have a 4 column PK identifier

UPDATE Pesate
SET flgagg = 'X'
FROM
Pesate INNER JOIN Pesate_temp
ON Pesate.esercz = Pesate_temp.esercz
AND Pesate.numset = Pesate_temp.numset
AND Pesate.numpro = Pesate_temp.numpro
AND Pesate.partita = Pesate_temp.partita

If you still get duplicate key violations than is it on the destination as
you pump the data in ?
If yes then is it possible that the key is not actually defined on the
source allowing you to have duplicate values at source?


--

----------------------------
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



"Nonno Oreste" <dffdfd (AT) dfd (DOT) dfd> wrote