Doubt in Lookup

Question

Doubt in Lookup

carlos.tapadinhas

Ten Centuries

Points: 1058
More actions
July 15, 2009 at 3:16 am

#133764

Hi to All
The Lookup allow us to know which rows already exists on a table right? But if i have a empty table, and a flat file to insert multiple rows with the same Key, why all rows are inserted on table... This Validation is made before the insert in destination or is made one row by one row ?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

You have not explained your problem very clearly.

Can you describe the requirement and post some sample data showing what's it's like as source and how you would like it to end up at the destination?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

carlos.tapadinhas Ten Centuries Points: 1058 More actions · Answer 2

Hi again, and thanks for the reply 🙂

For example....

i have a file with thousands of rows, and in that file i have a field named "RealEstateID", i have to insert in the table Realestate all rows that already not exist... but the problem appears here... in the beginning that Table is empty, so no realestateID exists at this moment, but in my file i have for example ten rows with same RealestateID, the rest of information dont interest in this case, and i just want to insert that ID one time... i hope i had explain better this time...

Thanks again, and waiting for u feedback

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

There is another (possibly faster) way of doing this which might help in your case.

Try creating a unique index on RealEstateID in your destination table.

CREATE UNIQUE NONCLUSTERED INDEX [IX_RealEstateID] ON

(

[RealEstateID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The critical bit of this is IGNORE_DUP_KEY = ON. This tells SQL Server to ignore attempts to insert duplicates (and not to error out).

If you use a SQL Server Native SQL destination adaptor and try running your package again after adding this index definition, you will see that only the first occurrence of any RealEstateID is inserted.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

carlos.tapadinhas Ten Centuries Points: 1058 More actions · Answer 4

Hi Phil,

Thanks for helping me with that one, i didnt remember to create the unique index 🙂 nice...

Is working now, but someday u have to explain me how i can do this without the index 🙂

Thanks again u were 5 stars..

Best Regards

Phil Parkin SSC Guru Points: 246962 More actions · Answer 5

Excellent! My pleasure.

You might find this link interesting - goes deeper than you wanted, but should still be informative for you ...

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

carlos.tapadinhas Ten Centuries Points: 1058 More actions · Answer 6

Hello again Phil,

But with that index my keys will be always 1,2,5,7... for example, not sequencial?

Phil Parkin SSC Guru Points: 246962 More actions · Answer 7

Should not affect that - I assume you are talking about an IDENTITY primary key?

There must be something else happening ... can you post a bit more detail?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

carlos.tapadinhas Ten Centuries Points: 1058 More actions · Answer 8

Hello Phil, Good Morning 🙂

I Think this "problem" i told u, is not from SSIS, but from SQL Server 🙂

For Example:

CREATE TABLE [dbo].[Test](

[TestKey] [int] IDENTITY(1,1) NOT NULL,

[TestID] [int] NULL,

CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED

(

[TestKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IDX_NC_TestID] ON Test

(

TestID ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

INSERT INTO Test (TestID)

SELECT 1

INSERT INTO Test (TestID)

SELECT 2

INSERT INTO Test (TestID)

SELECT 2

INSERT INTO Test (TestID)

SELECT 3

select * from test

TestKey TestID

1 1

2 2

4 3

DROP TABLE TEST

The TestKey = 3 is missing, when i try to insert TestID 2 again (Duplicate key was ignored.)

Just if i drop the primarykey from the table and just let the TestKey with Identity...

Cheers

DROP TABLE TEST

Phil Parkin SSC Guru Points: 246962 More actions · Answer 9

I agree with your findings - it's as if the insert happens and then rolls back, using up a number in the process.

I do not know whether that behaviour can be changed.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

carlos.tapadinhas Ten Centuries Points: 1058 More actions · Answer 10

carlos.tapadinhas

Ten Centuries

Points: 1058

July 16, 2009 at 4:41 am

#1025529

Me Neither 🙂

Thanks again for all