Hi,
I need to launch a SSIS process that has several errors (the most common is insert records with a PK that already exists in the destination table). My process may continue to the end despite these errors, in order to see them in the log when the process has finished.
I have set "MaximunErrorCount" to 999999 , but the process stop the first time i attempt to write a duplaicate PK.
I have also set MaximunErrorCount of every Data flow taks to this value...
What's going wrong?
Thanks!
Hi Alberttoim,
I am not sure of this.
But there is a better solution, If you want to get unique rows finally in to your destination table:
- First output to a Staging Table whose structure is same as your Destination table and dedupe them.
Thanks
Subhash Subramanyam
|||
Hi Subhash,
The thing is the source table is huge, and doing this is so expensive... in addition to this i want to know how to manage errors since this is not the only error i can find in a future.
I have try to "omit error" but in this case the error is not recorded in the log.
I theory , if i set MaximumErrorCount to a high value the process should continue executing ,is'nt it? What is going worng?
|||Albertoim,
Have you read through this thread? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||
Yes, i have read it. I do a lookup before insert the record, the problem is that i am not using a for each bucle, i am treating a bunch of rows.. thats why the lookup transformation doesnt find any record with this PK because it actually doesnt already exists fisically in the database.
Anyway, i need to know how to manage errors, how to make the process continue executing in spite of errors.
|||You can redirect errors out of the OLE DB Destination component. That will allow you to redirect them (or even ignore if you want) without raising a package error.|||And a lookup would work if your destination is failing because of primary key violations. If you are expecting the lookup to refresh with each inserted record from the data flow, you can setup the lookup to not cache it's lookup results, which means that every time a new records gets inserted, the next record through the lookup could find it.|||
Thanks Phil,
I have been doing this to prove that the whole package works ok, but now i am trying to use the other solution because the flow is quite big and i have to redirect errors in so many places... the package becomes too confusing.
The question is, can't i allow the package continue executing when this error happens and record it in the log?
If i use an event handler, can i send to it any info (columns of the duplicate record for example)... when the event is triggered the task stop executing?
Thanks
|||
Phil, regarding your second solution...
Thats the situation: Source table has info that already exists in the destination... i have to update/add records in the destination deppending if the record already exists or not.
If i disable cache and in the source table two records have the same PK, in the destination , in the first round the record will be inserted, and in the second will be updated... no error but i cant detect what is happening. I need to detect these errors to advice the user to correct it, i can't touch the source table...
In addition to this, i think disabling cache of lookup transformations that manage a great amount of info will produce a low performance.. am i wrong?
I simply need to allow the process to support continue executing in spite of errors and record them on a log...
|||
 Albertoim wrote: |
| |
| Phil, regarding your second solution... Thats the situation: Source table has info that already exists in the destination... i have to update/add records in the destination deppending if the record already exists or not. If i disable cache and in the source table two records have the same PK, in the destination , the first round the record will be inserted, and in the second will be updated... no error but i cant detect what is happening. I need to detect these errors to advice the user to correct it, i can't touch the source table... In addition to this, i think disabling cache of lookup transformations that manage a great amount of info will produce a low performance.. am i wrong? I simply need to allow the process to support continue executing in spite of errors and record them on a log... |
Why can't you redirect the errors as is the appropriate solution?|||I can do that, but i am trying to find the other way to do that because introducing a lot of redirections makes the flow so confusing (i have to introduce them in many places)... the question is if i can ignore errors, continue executing but record them on the log (i supposed MaximumErrorCount property is for that purpose).|||
| Albertoim wrote: |
| |
| I can do that, but i am trying to find the other way to do that because introducing a lot of redirections makes the flow so confusing (i have to introduce them in many places)... the question is if i can ignore errors, continue executing but record them on the log (i supposed MaximumErrorCount property is for that purpose). |
It's the "record them on the log" that's causing you problems. You want to continue processing, yet capture the error rows, but do not want to redirect them. I fail to see how you can do this without redirecting the errors.
Do you want to know which rows were in error, or just the error message?|||
Phil,
The main problem i have is a cant let the proccess continue when the error happens... despite i set MaximumErrorCount value to 10000 the process stop.
Once i solve it i would see how to get the row that produce the error: maybe with a custom log, creating an event handler... whatever... but firstly i would like to solve the other issue regardless of knowing which row is duplicated... just see the error message in the log.
Thank youuu
|||Make sure that FailPackageOnFailure and FailParentOnFailure aren't set to TRUE for the data flow.|||
Both properties are set to False... any other hint?
Thanks you very much for your help Phil...
Edit: i've been doing tests in a crappy package and the same problem... execution stops when this specific error happens. MaximunErrorCount set to 100, FailPackageOnFailure = False and failParentOnFailure = False. I have seen the problem is not the whole package stops, its the Dataflow that contains the error what is sttoping, only it...
|||You should set the MaximumErrorNumber to zero of the parent task. It's better than setting a high value. I Hope it helps.
sql
