How to capture error log in a table in BODS

[former_member186897]

I will be walking you through (step by step procedure) how we can capture error messages if any dataflow fails in a Job. I have taken a simple example with few columns to demonstrate.

Step 1: Create a Job and name it as ‘ERROR_LOG_JOB’

Step 2: Declare following four global variables at the Job level. Refer the screen shot below for Name and data types.

Step 3: Drag a Try Block, Dataflow and Catch block in work area and connect them as shown in diagram below. Inside dataflow you can drag any existing table in your repository as a source and populate few columns to a target table. Make sure target table is a permanent table. This is just for demo.

Step 4: Open the Catch block and Drag one script inside Catch Block and name it as shown in below diagram.

Step 5: Open the scrip and write below code inside as shown in the diagram below.

The above script is to populate the values in global variables using some in-built BODS functions as well as calling a custom function to log the errors into a permanent table. This function does not exits at this moment. We will be creating this function in later steps.

Step 6: Go to Custom Function section in your repository and create a new custom function and name it as under.

Step 7: Click next in above dialog box and write the below code inside the function. You need to declare parameters and local variables as shown in the editor below. Keep the datatypes of these parameters and local variables what we have for global variables in setp 2. Validate the function and save it.

Step 8: Now your function is ready to use. Considering that you have SQL Server as a database where you want to capture these errors in a table. Create a table to store the information.

CREATE TABLE [dbo].[ERROR_LOG](

      [SEQ_NO] [int] IDENTITY(1,1) NOT NULL,

      [ERROR_NUMBER] [int] NULL,

      [ERROR_CONTEXT] [varchar](512) NULL,

      [ERROR_MESSAGE] [varchar](512) NULL,

      [ERROR_TIMESTAMP] [VARCHAR] (512) NULL

)

You may change the datastore as per your requirement. I have taken ETL_CTRL as a datastore in above function which is connected to a SQL Server Database where above table is being created.

Step 9: Just to make sure that dataflow is failing, we will be forcing it to throw an error at run time. Inside your dataflow use permanent target table. Now double click the target table and add one text line below existing comment under load triggers tab. Refer below screen shot. This is one way to throw an error in a dataflow at run time.

Step 10: Now your Job is ready to execute. Save and Execute your Job. You should get an error message  monitor log. Open the table in your database and check if error log information is populated. Error Log shall look like as shown below.

ERROR_LOG table shall capture the same error message in a table as under.

Hope this helps. In case you face any issue, do let me know.