Logging in SSIS
By default whenever package fails, error will be logged in Event viewer.
But still, we can maintain the complete execution history in customized way.
In Text File Log provider, you have specify text file. And it will start logging in that text file. It will append the text at every time.
In SQL Server Log provider, it will create a table called “ssislog”. And it will start inserting records.
Mainly, we are intersted in particular error. So we can do following settings.
Ssislog table will have records like this.
SSIS Logging
Introduction
Auditing is a very important part of any task. When we are working with SSIS ETL process we want to audit the entire process. It is very much important to keep track of the flow within the ETL process. It is very much important to troubleshoot the problem when ETL process executed. This article is related to SSIS Logging process.
Example to understand what Logging process is
Suppose we have different tasks in the SSIS ETL process and when the package is executed some tasks fails. By logging we can understand clearly which tasks are succeeded and which one is failed. For the failed tasks we need to know why the tasks are failed.
How we can Activate the Logging process
To activate the logging process we must go to the SSIS menu and then select the Logging.
SSIS menu à Logging…
When we select the logging… from the SSIS menu the Configure SSIS log windows appears.
In the provider type of Providers and Logs we have five options to select
a. SSIS Log providers for windows Event log
b. SSIS Log providers for Text file
c. SSIS Log providers for XML file
d. SSIS Log providers for SQL Server
e. SSIS Log providers for SQL Server Profiler
SSIS Log providers for windows Event log
The Windows Event log provider, which writes entries to the Application log in the Windows Event log on the local computer.
SSIS Log providers for Text file
The Text File log provider, which writes log entries to ASCII text files in a comma-separated value (CSV) format. The default file name extension for this provider is .log.
SSIS Log providers for XML file
The XML File log provider, which writes log files to an XML file. The default file name extension for this provider is .xml.
SSIS Log providers for SQL Server
The SQL Server log provider, which writes log entries to the sysdtslog90 table in a SQL Server database.
SSIS Log providers for SQL Server Profiler
The SQL Server Profiler log provider, which writes traces that you can view using SQL Server Profiler. The default file name extension for this provider is .trc.
Here for my article I am choosing the SSIS Log providers for Text file.
And then click the Add button. Then I click the configuration and choose the new connection … The file connection manager editors appear.
Then we go to the details tab. It contains events and its description. For this article we choose some events like
- OnPreExecute
- OnWarning
- OnError
- OnPostExecute
Now we execute this package and open the text file.
For reference
Hope you like it.
