8

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?

goofyui
  • 3,362
  • 20
  • 72
  • 128

2 Answers2

23

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:

1

Screenshot #2:

2

Screenshot #3:

3

Screenshot #4:

4

Screenshot #5:

5

Screenshot #6:

6

3

It's very easy to log errors in SSIS. Go to Event Handlers tab, select OnError from dropdown. Now here you can send email on any error or you can develop your own custom logic to log error into DB or write to text file.

Also, you can choose from existing logging mechanism available in SSIS. Click on menu "SSIS", you will see first option as Logging then you will see different options to log errors/warnings and whole lot of information you need.

pramodtech
  • 6,300
  • 18
  • 72
  • 111