sql server 2005 - How to create an error log or custom error log within an SSIS Package?

Question

I am working on SQL 2008 - SSIS Package. I am executing the package from C# code. It's simple Flat File ( .csv) to SQL Table.

From the C# code, I get execution result as SUCCESS but inside the package Transactions have not taken, so values are not actually exported.

I need to debug it, by reading the log file.

How to create Error Log or Log File inside the Package?

Answer 1

Here is one way of configuring logging feature in SSIS. In this example, I have Send Mail Task configured to fail and I have enabled the SQL Server based logging which will store the OnError and OnTaskFailed messages when the package fails. There are other events that could be of interest to you as well. In the projects that I have worked, these two tasks have helped me to identify most of the issues that occur in SSIS packages.

Step-by-step process:

1. SSIS package in this example contains a Send Mail Task configured as shown in screenshot #1. It is configured to fail so we can see some error messages in the log table.
2. Click on the SSIS package.
3. On the menus, select SSIS --> Logging... Refer screenshot #2.
4. On the Configure SSIS Logs: dialog, select the provider type and click Add. I have chosen SQL Server for this example. Check the Name checkbox and provide the data source under Configuration column. Here SQLServer is the name of the connection manager. SSIS will create a table named dbo.sysssislog and stored procedure dbo.sp_ssis_addlogentry in the database that you selected. Refer screenshot #3 below.
5. If you need to capture the errors, select the checkbox OnError and OnTaskFailed. Refer screenshot #4 below.
6. Sample package execution within data flow task is shown in screenshot #5 below.
7. Sample output of the log table dbo.sysssislog is shown in screenshot #6 below. I have only displayed few columns id, event, source and message. There are other columns in the table. Message column contains the error message, here in this case the server name mentioned in the Send Mail Task is wrong. Source column contains the task where it failed. Here in this case, the package name is SSISLoggingExample and Send Mail Task is named as Email Task. Error messages will bubble up from task to the package level. Hence, the error message is logged twice under the task as well as at the package level.

Hope that helps.

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6: