Hi all,

I have created a DTS package to transfer data from a some tables in a Access database to SQL server.
I use a transform data task and specifiy a source and destination table and use a activeX script perform the source/destination mapping.
Because the data source is a union of some tables, some of the value violate the primary key contrainst of the destination table. To overcome this, I have created a lookup query in the transform data task to check whether the primary is already existed, if yes, then skip the current record, otherwise, insert in to the destination table.

But I find that if the source table contain 2 records with the same primary key, the lookup query also return false for the checking and so thuse cause the package fail due to the violation of primary key.
What I want to ask is if the lookup query can read the data just added to the destination table in the same batch ? Or the lookup query only can read committed data even the uncommitted data is within the same session ?

Thank in advance.

Regards,
ong

I would sort the data out in the source first

UNION will remove duplicates that are duplicates due to the Unioning.

DISTINCT will make a row distinct.

How can you have duplicate Key values ?

--

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



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

this, I have created a lookup query in the transform data task to check
whether the primary is already existed, if yes, then skip the current
record, otherwise, insert in to the destination table.
thuse cause the package fail due to the violation of primary key.
committed data even the uncommitted data is within the same session ?

Hi Allan,

The source do have some duplicate records because the design of Access application is quite a little bit different from the design of the new SQL server application
There are 3 tables (each table serve for different purpose) storing the records of some kind of contact list in the Access application, some of the records exist in all the 3 tables, for example, all the tables contain a "microsoft" contact information, and all tables use the "microsoft" as the key value
Therefore, when I transform records from Access to SQL server, the duplicate key still exist.
I have try to use Union and distinct, but the only duplicate value is just the key value, the other column value is different
for example, there is a address column, and 1 table contain "USA" as address, 1 table contain "U.S.A." as address, the 1 table contain "US" as address. Therefore, both union and distinct cannot work
I finally use a lookup query to achieve the goal, but it fail and alway say that the table contain no row due to the transfer data task not yet finish


----- Allan Mitchell wrote: ----

I would sort the data out in the source firs

UNION will remove duplicates that are duplicates due to the Unioning

DISTINCT will make a row distinct

How can you have duplicate Key values

--

---------------------------
Allan Mitchell (Microsoft SQL Server MVP
MCSE,MCDB
www.SQLDTS.co
I support PASS - the definitive, global communit
for SQL Server professionals - http://www.sqlpass.or



"ong" <anonymous (AT) discussions (DOT) microsoft.com> wrote in messag
news:C26E3721-8548-4A8B-BBCF-D0EA24E62EBE (AT) microsoft (DOT) com..
this, I have created a lookup query in the transform data task to chec
whether the primary is already existed, if yes, then skip the curren
record, otherwise, insert in to the destination table
thuse cause the package fail due to the violation of primary key
committed data even the uncommitted data is within the same session

UURRGGGHH. OK Ideally you would have a deduping program to do this. Yukon
is gonna light your fire for this. For your Lookup Query assign a different
connection object not the one you are using in the transformation task.

--
--

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

"ong" <on_g (AT) sinatown (DOT) com> wrote

server application.
records exist in all the 3 tables, for example, all the tables contain a
"microsoft" contact information, and all tables use the "microsoft" as the
key value.
address. Therefore, both union and distinct cannot work.
finish.

Hi Allan

Thank for your reply
I have created a different connection to the Lookup Query, but it seems the key constraint violation still occur

Maybe I explain what I gonna to do in more details
I have 3 tables in Access to store some contact information of my client. Now I want to transfer and combine all the data from the 3 Access table to 1 SQL Server table. Since some records is duplicated after union the Access tables, they have the same primary key but different column values, for example, the "microsoft" contact information. Therefore , when I run my DTS package, it will fail due to the key constraint violation error
So I created a LookUp Query in the transformation task, the reason is I supposed the query can check the destination table whether the current record already exists in the table, if yes, then skip it, otherwise, add it
For example, when the transformation task first read the "microsoft" record, then it first check for the destination table to see if the records exist, if no, it return "record not find", then the task insert it to the destination table. And the task continue, when the second time the task read again the other "microsoft" record, it perform a lookup query to the destination table and should return "record find",then the task skip the record
Now the problem is when the DTS package run, no matter the "microsoft" record is already inserted to the table from the first time the task read the record, the lookup query alway return "record not find" when the task read the "microsoft" record second time
Is it related to the transaction problem ? Since the task still running and not yet commit the changes, so the lookup query cannot read the "dirty" data

Thank in advanc
on

----- Allan Mitchell wrote: ----

UURRGGGHH. OK Ideally you would have a deduping program to do this. Yuko
is gonna light your fire for this. For your Lookup Query assign a differen
connection object not the one you are using in the transformation task

--
-

Allan Mitchell (Microsoft SQL Server MVP
MCSE,MCDB
www.SQLDTS.co
I support PASS - the definitive, global communit
for SQL Server professionals - http://www.sqlpass.or

"ong" <on_g (AT) sinatown (DOT) com> wrote in messag
news:AEFFB477-DC7F-47F8-BB8A-CE4ED5035DD3 (AT) microsoft (DOT) com..
server application
records exist in all the 3 tables, for example, all the tables contain
"microsoft" contact information, and all tables use the "microsoft" as th
key value
address. Therefore, both union and distinct cannot work
finish

OK

Scrubbing time. Do you care which "Microsoft" row gets inserted ? We need
to dedupe these rows. Insert them into SQL Server into a table that has the
sme PK as before. Do not use a PK but use an index with IGNORE_DUP_KEY
i.e

CREATE TABLE Foo(ABC int)
CREATE UNIQUE INDEX idx_Filter_Dups ON Foo(ABC) WITH IGNORE_DUP_KEY
INSERT Foo VALUES(1)
INSERT Foo VALUES(1)


(1 row(s) affected)

Server: Msg 3604, Level 16, State 1, Line 2
Duplicate key was ignored.


select * from Foo

ABC
-----------
1

(1 row(s) affected)


Or you could pump each of your 3 tables into the same table but add an
INDENTITY() column. You would then take over using DTS only those records
that had the lowest IDENTITY() column value for each of your previous PK
values i.e.

CREATE TABLE PK(Col1 INT IDENTITY(1,1),colYourPK
varchar(50)....................)

--Do your insert into this

SELECT colYourPK................................
FROM PK T1 WHERE Col1 = (SELECT MIN(Col1) FROM PK T2 WHERE T2.ColYourPK =
T1.ColYourPK)





--
--

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

"ong" <on_g (AT) sinatown (DOT) com> wrote

SQL Server table. Since some records is duplicated after union the Access
tables, they have the same primary key but different column values, for
example, the "microsoft" contact information. Therefore , when I run my DTS
package, it will fail due to the key constraint violation error.
record already exists in the table, if yes, then skip it, otherwise, add it.
exist, if no, it return "record not find", then the task insert it to the
destination table. And the task continue, when the second time the task read
again the other "microsoft" record, it perform a lookup query to the
destination table and should return "record find",then the task skip the
record.
the record, the lookup query alway return "record not find" when the task
read the "microsoft" record second time.
data ?