How do I capture IMan service exceptions? (5 replies)
John, IMan doesn't typically wrap exception as they could be almost anything...and trying to decipher any exception to wrap and rethrow would be fiendishly difficult. How do we tell the difference between a SQL error, a network error, some other fault or a combination. Our approach is to let the exception bubble to surface.
SQL Server failure error will propagate through to the exception....but trying to look for it may be difficult as the exception could either be from the Sage300 connector, or say a lookup.
If it's from something which touches the database directly (Lookup, DB Writer) you will get the actual SQL exception, if it's from a connector you'll get whatever the connector spits out (which is probably the exception, possibly not).
FYI: The only direct database connectivity IMan makes is during job initialisation...all audit/logs are written to by the Log Service, and IntEngine via MSMQ. The reason for using MSMQ, is for the same scenario as you are guarding against....a database failure during an integration.
Do you have any best practices or design guidelines for recognizing and acting on exceptions thrown by IMan?
John, we probably need to put together a document/FAQ like what you're looking after.
If you have a complex integration covering multiple transactions, you need to think about what occurs where there's an error in each step.
If it's a catastrophic error....like a SQL failure, we really don't have a scenario for this. I would inform the customer to fix the underlying issues so it doesn't happen again.
If it's a non-catastrophic, for example a transaction failing a typical strategy is to:
- Set Action on 'Transform Error' to 'Reject Record'.
- Place two filters after the connector. In one Filter drop any IMan records which couldn't be processed to continue processing the other transactions. In the other Filter drop any successful transaction to leave only the erroneous ones....then with that branch write to an error/staging table/etc.
There's an FAQ which describes strategy:
http://www.realisable.co.uk/support/faqs/file-routing-based-records-success-failure/
Thanks
John, everytime IMan ends it logs an entry to the AUDITLOG table where:
LOGTYPE = 5 (Signifies the end of an integration...successful or otherwise)
RESULTREASON = Job [%1] has aborted due to a transform failure. (where %1 is the id of the job/integration).
On the next run you could do a lookup against the AUDITLOG table to return the last scheduled integration (the preview service writes to the table, so you need to look for completion entries vs. joining the NEXTNO value in the JOBDETAIL).
Select Clause - top 1 *
From Clause - AUDITLOG
Where Clause - LOGTYPE = 5 and PROCESSID ='%1' orderby [TIMESTAMP] desc
The LASTRUNDATE on the JOBDETAIL table is also updated on successful completion.
Closed due to inactivity.
We have a large, complex job that creates transactions in Sage 300. A single input record can result in zero, one, or two credit notes, and an invoice. If SQL Server fails between the time the credit notes are created and the time invoices should be created, we need to keep IMan from executing the job in subsequent scheduled cycles.
If we can recognize an exception thrown by the IMan service we could stop subsequent executions of the job from creating further transactions in Sage 300, and send emergency emails.
Do you have any best practices or design guidelines for recognizing and acting on exceptions thrown by the IMan service itself?