MaximunErrorCount issue

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

Maximum number of updates within a Transaction

Good day to all

SQL Server 2000.

I have a problem with a piece of code, which updates some tables using transaction. This process brings the program to a halt when updating large files.

With smaller files, the process finishes without problems.

I have noticed that, if a comment out the "begin transaction" and respective "commit", the same code executes without problems, even when updating large files.

I suspect that there is a limit on the number of records a transaction can hold before a commit is issued. I am surprised however, that SQL Server halts, without messages or warnings.

Is this a configuration issue? If there is a limit on the number of records a transaction can hold, what is this limit:? Anything I can do to have a warning form SQL Server when a situation like this is reached (or indeed to avoid this situation) ?

Thank you in advance for any help

Cecil Ricardo

hi cecil,

my guess is that the transaction is being involved in a recursive operation which means it triggers something and then it triggers back the transaction in a continous loop. thats why it halts your system

With out the begin transaction and commit transaction clause recursive operations end until 32 layers deep. Which cause your batch or sp to commit sucessfully

regards,

joey

|||

There are no limits to the number of rows you can commit in a transaction. It is bound by the size of your transaction log file(s). Did you check for any error messages in the errorlog? Note that the delay you may have observed was probably due to the commit operation itself. Depending on the number of changes you did within the transaction a commit/rollback can take a long time. And the semantics for using begin / commit for say 100 insert statements is different from executing 100 individual insert statements. So you cannot really compare the two approaches. By default, if you do not specify a begin/commit transaction the statement runs in auto-commit mode meaning each statement is a transaction by itself. Often, you will get better performance by using begin transaction/commit due to buffering of log writes. The batch size however depends on lot of factors. So do the following:

1. If you really need to use a transaction for updating a large table then ensure that you have enough space in your drives to account for log growth. Time for commit/rollback depends on the changes

2. If you just need to update a large number of rows then you can use a batch mechanims where you commit only say 1000 rows at a time and use a simple loop. You can use SET ROWCOUNT to restrict the rows affected by DML statements in older versions of SQL Server and use TOP clause in SQL Server 2005

3. Transactional consistency and semantics is different if you run a bunch of DML statements within a transaction vs running each DML statement separately. They are not the same so you need to be aware of the differences. See Books Online topics on auto-commit transactions and user specified transactions.

|||

I have checked that. It is not the case.

But thank you for the valuable information about the end of recursive operations ending at 32 layers deep.

Thank you Joey

|||

Thank you so much for the comprehensive explanation, Umachandar.

I have applyied your suggestion aof commiting smaller chunks of rows. It worked fine.