February 2, 2017 at 8:29 am
I've got an SSIS package with an "Execute SQL task" that calls a stored procedure to insert rows into a dimension table.
The table has a surrogate primary key that has the identity attribute turned on. When I run the SSIS package the first time, the Execute SQL task fails due to a duplicate primary key violation on the value 0 (zero). This is after successfully inserting a couple of thousand rows.
Since the primary key has an identity attribute that is seeded at 1 and thousands of rows have been inserted, how is this possible?
If I delete the rows from the dimension and run the package a second time, it runs to completion.
Needless to say, I'm a little baffled.
"Beliefs" get in the way of learning.
February 2, 2017 at 8:37 am
Robert Frasca - Thursday, February 2, 2017 8:29 AMI've got an SSIS package with an "Execute SQL task" that calls a stored procedure to insert rows into a dimension table.The table has a surrogate primary key that has the identity attribute turned on. When I run the SSIS package the first time, the Execute SQL task fails due to a duplicate primary key violation on the value 0 (zero). This is after successfully inserting a couple of thousand rows.
Since the primary key has an identity attribute that is seeded at 1 and thousands of rows have been inserted, how is this possible?
If I delete the rows from the dimension and run the package a second time, it runs to completion.
Needless to say, I'm a little baffled.
What makes you think that SSIS is causing the error? If is ExecSQL task is purely executing a proc, I would be looking at the proc first.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 2, 2017 at 9:01 am
I'm getting the error from an SSIS package so I referred to it that way in the title. There's nothing wrong with the proc either. That's kind of the point. It's a nonsensical error that, as far as I can tell, shouldn't be possible in the scenario described.
"Beliefs" get in the way of learning.
February 2, 2017 at 9:09 am
Robert Frasca - Thursday, February 2, 2017 9:01 AMI'm getting the error from an SSIS package so I referred to it that way in the title. There's nothing wrong with the proc either. That's kind of the point. It's a nonsensical error that, as far as I can tell, shouldn't be possible in the scenario described.
So are you saying that if you execute the proc in SSMS in exactly the same context as SSIS executes it, you would not get an error in SSMS, but you would get one in SSIS?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 2, 2017 at 9:10 am
Phil Parkin - Thursday, February 2, 2017 9:09 AMRobert Frasca - Thursday, February 2, 2017 9:01 AMI'm getting the error from an SSIS package so I referred to it that way in the title. There's nothing wrong with the proc either. That's kind of the point. It's a nonsensical error that, as far as I can tell, shouldn't be possible in the scenario described.So are you saying that if you execute the proc in SSMS in exactly the same context as SSIS executes it, you would not get an error in SSMS, but you would get one in SSIS?
How do you know that the proc successfully inserted 2,000 rows already? Is the proc doing the inserts in batches?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 2, 2017 at 9:34 am
No, that's not what I said at all. The proc always works, including in SSIS, except in one scenario. I apologize for the misleading subject.
The scenario is that I'm installing the database and ssis stuff from dacpac and ispac's respectively and then running the job. It always fails the first time I run it.
Turns out, it was DBCC CHECKIDENT that was problematic. In the previous EXECSQL task I had SET IDENTITY_INSERT ON for that table and inserted one row with a value of 0 as the primary key and a description of "Unknown" record. Then I used DBCC CHECKIDENT('Mytable',RESEED, 0) to reseed the table assuming that the next value would be RESEED value + 1 as it is documented. Turns out, in the fine print of the documentation, if you haven't inserted a row (with IDENTITY_INSERT OFF) before the call to DBCC CHECKIDENT it will use the value you passed in rather than RESEED value + 1.
Ironically, I now have no id of 1, it goes from 0 to 2. Oh well, I can live with that. Still don't understand how it could insert a couple of thousand records before it decided to crap out.
"Beliefs" get in the way of learning.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply