a) File Location
In order to route failed messages to a file location, we have to follow two important steps.
i. Enable failed message routing at send and receive ports.
ii. Create filters at the send port to route error messages based on the promoted error properties.
i. Enabling failed message routing at send port
· Double click on send port to open the send port dialogue box.
· Select Transport Advanced Options on the left pane
· Select “Enable Routing for failed Messages” in the right pane.
Enabling failed message routing at receive port
· Double click on receive port to open the receive port dialogue box.
· Select General Options on the left pane
· Select “Enable Routing for failed Messages” in the right pane.
ii. Create filters at the send port to route error messages based on the promoted error properties.
· Double click on send port to open the send port dialogue box.
· Select Filters Options on the left pane
· Select BTS.MessageTye from Property drop down box.
· Select = = from Operator drop down box.
· Type “FailedMessage” in Value(without double quotes)
Messages that fail at the receive/send ports for reasons such as unexpected delimiter, data type mismatch, missing data, and so on are pushed to the predefined folder in the BizTalk server machine. Thus we can ensure that there may not be chance for any data loss.
b) Database Table
Messages which fail after passing through the SQL Adapter can be stored in a database table. For this, we need to create a table in the respective database with following specifications.
ErrorMsgId bigint
ErrorNumber int
ErrorSeverity int
ErrorState int
ErrorProcedure nvarchar(128)
ErrorLine int
ErrorMessage nvarchar(4000)
ErrorOccuredDate datetime
SPName nvarchar(50)
Also create a stored procedure that writes the error messages to the table.
CREATE PROCEDURE [dbo].[usp_GetErrorInfo]
@SpName nvarchar(50)
AS
INSERT INTO [GrimcoWebstore_transactions].[dbo].[cust_ExceptionLog]
([ErrorNumber]
,[ErrorSeverity]
,[ErrorState]
,[ErrorProcedure]
,[ErrorLine]
,[ErrorMessage]
,[ErrorOccuredDate]
,[SPName])
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage,
getdate() As ErrorOccuredDate,
@SpName As SPName
Call this procedure within the try catch block of any stored procedure that is looked up by the SQL Adapter.
Messages which fail to update/insert database tables due to mandatory data missing, data type mismatch, etc., are logged into “cust_ExceptionLog” table. This error log can be viewed using MS SQL server Management studio.
Ø Start->Programs->Microsoft SQL Server 2005->SQL Server Management Studio
Ø Open “cust_ExceptionLog” table