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?
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:
Name
checkbox and provide the data source underConfiguration
column. Here SQLServer is the name of the connection manager. SSIS will create a table nameddbo.sysssislog
and stored proceduredbo.sp_ssis_addlogentry
in the database that you selected. Refer screenshot #3 below.OnError
andOnTaskFailed
. Refer screenshot #4 below.dbo.sysssislog
is shown in screenshot #6 below. I have only displayed few columnsid
,event
,source
andmessage
. 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:
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.