February 9, 2009 at 11:17 am
Hi All,
I have a SSIS package which is failing because of the "Duplicate Key was ignored" warning/Error.
This is the table structure
CREATE TABLE [dbo].[A1](
[aa] [char](2) NULL,
[bb] [char](2) NULL
) ON [PRIMARY]
CREATE UNIQUE NONCLUSTERED INDEX [abcd] ON [dbo].[A1]
(
[aa] ASC,
[bb] 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]
CREATE TABLE [dbo].[B1](
[aa] [char](2) NULL,
[bb] [char](2) NULL
) ON [PRIMARY]
insert into B1 values('1','2')
insert into B1 values('1','2')
-------------------------------------------------------------------------------------
Now if I create an "Execute SQL task" in SSIS as
--------------------
Insert into A1
select * from B1
---------------------
The package fails because of this error "Duplicate key was ignored". I was under the impression that this is a warning and not an error because when I run this query separately it runs fine with this result.
-----------------------
Duplicate key was ignored.
(1 row(s) affected)
-----------------------
so clearly its doing what its supposed to do: ignore duplicate values and insert unique values.
My machine has a standard edition of SQL 2005. I tried duplicating the same on an enterprise version and the package executes with no error.
Any thoughts? I am sort of clueless here?
Thanks.
February 9, 2009 at 11:23 am
SSIS is treating it as an error. Often, text returned that way from the query will be handled as an error.
Is there a reason to create a unique index that isn't, in fact, unique?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2009 at 11:32 am
Reason for making a non-clustered unique index is that the source table can have loads of redundant data so I wanted to handle this on a database level instead of writing a query like:
Insert into A1
select * from B1
where
(aa + '_' + bb) not in
(
Select (aa + '_' + bb) from A1
)
which would become inefficient if the number of columns become more than 10 (in the real table).
As a matter of fact I had this approach before and was working fine but was way too slow.
February 9, 2009 at 11:37 am
I guess I'd rather have ignore_dup_key off and have the insert code validate the data with an Exception operation. That's going to be easier to document, and it's going to generally work better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2009 at 11:54 am
Thanks for the reply.
What about the fact that the SSIS package is working fine in the enterprise edition but failing in the standard edition ?
February 10, 2009 at 7:56 am
No clue on that one. I've never seen that happen before.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply