February 10, 2011 at 7:58 am
Hello All,
one of my schedule job starts failing since last night.
its a ssis package.. its been running fine for long since last night.
this ssis package basically movies data from one server to the other ( let's say Server A to Server B).
thats the message that I am getting .
Message
Executed as user: E_E\MSSQL2k5login. ... Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:10:00 AM Error: 2011-02-10 09:10:00.87 Code: 0x00000000 Source: Insert UserData Description: Cannot insert duplicate key row in object 'dbo.ep_userdata' with unique index 'idxEMAIL'. End Error Error: 2011-02-10 09:10:00.87 Code: 0xC002F210 Source: Insert UserData Execute SQL Task Description: Executing the query "Insert ep_userdata select * FROM ep_userdata_tmp AS tmp Where tmp.userid not in (select userid from epal_userdata As dmz) " failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:10:00 AM Finished: 9:10:00 AM Elaps. The step failed.
first i dont get it why it suddenly stopped working. been running for a while now. I checked with the other team and no one made any changed on the windows server side.
Is it a connection issue in the ssis package
any help in this matter is helpful.
February 10, 2011 at 8:06 am
From the text of the error, I'd guess that you have a unique constraint on an EMAIL column and you are attempting to insert a record which violates the constraint.
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 10, 2011 at 8:30 am
First of all thank you so much for fast reply.
I was going towards the same direction but ... what stopped me was this is the same code and same job that is been running for 6 months.. nothing changed on the production server since i am the dba .. and if any thing would have been changed on the table or col , it would have gone through to me ( i didn't make any changed) so why it stopped running yesterday.
February 10, 2011 at 8:39 am
Only the data has to change to create this problem... and I'm guessing that is not under your control. Or perhaps there is a problem where there are missing e-mail addresses (which could also violate the constraint).
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 10, 2011 at 8:44 am
the user does have datareader and datawriter permission on the table/database.
so how can i go about it...meaning how to fix this constraint/duplicate key issue..
sry i am a bit clueless here. any path/direction is helpful.
February 10, 2011 at 9:19 am
Apologies if I'm stating the obvious here. It's difficult to go into any detail without knowing more about your systems - but your package appears to be failing as it tries to insert data in one table from another source.
You need to compare the data in the other source with what is already in the destination table & identify which row or rows are causing the issue. Then you need to decide what to do about it (Delete erroneous source data? Amend/correct it? Change package logic to handle dups gracefully? Change destination schema?)
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 10, 2011 at 9:40 am
Thanks for the answer.... i am going to look in to what u have suggested.
also it appears its the script that is failling to run in the package
Insert epal_userdata
select *
FROM epal_userdata_tmp AS tmp
Where tmp.userid not in (select userid from epal_userdata As dmz)
can you or any one explain what this doing?? as far as i can tell
inserting in epal_userdata table from epal_userdata_tmp AS tmp
but i am not sure about the last line
"Where tmp.userid not in (select userid from epal_userdata As dmz)"
Thanks in advance.
February 10, 2011 at 9:45 am
Yeah - that last line is trying to avoid inserting duplicates.
It says "don't insert lines where the userid already exists in the target table".
So if a userid has changed for a user, but their e-mail address has not - maybe that could be it.
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 10, 2011 at 11:34 am
Hello,
First of all thank you very much for all your help.... ur took time to listen to my adam/eve story when I was totally freaking out.
good news is i was able to fix the issue ... apparently it turned out a connection problem.
the package was using encryption with user key..... and some thing changed and sql task was no longer connection to the table.
i switched it to package password , redeployed it on the server and it ran fine
Again thanks for all your help.
February 10, 2011 at 11:45 am
Thanks for posting back - that's good news and I am happy to have at least tried to help.
Given the problem's resolution, that error message is very misleading!
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply