Logging in SSIS

Logging

It is very difficult to identify Run Time error and performance issue. Logging is s method on which we can record the events which occurs during the code execution. Logging is the very useful feature in SSIS. With the help of logging we can capture the run time information of the package and tasks which helps us to audit and troubleshoot a package every time it’s running. We can capture the log on Package or a particular task label.

SSIS provides different way to logging such as

Ø  Text file logging

Ø  SQL server

Ø  SQL server profiler

Ø  Windows Event log

Ø  XML file

Ø  Custom logging

Let's learn each type of logging method step by step.

Text File Logging

Here I am log both package and task label.

Open SSDT

I am taking For each loop container.

Set the properties of For loop container

Creating a package label variable named Sno Data type is Int.

In For loop container I am taking script task for displaying the simple value.

Here I am setting the properties of the Script task.

Writing the script

public void Main()         {             // TODO: Add your code here             MessageBox.Show(Dts.Variables["User::Sno"].Value.ToString()); Dts.TaskResult = (int)ScriptResults.Success;         }

Now I am executing the package.

Package completed successfully.

Implementing logging on the package

There are two way to get the logging option

1.       Right click on the package we you will the logging on top.

2.       In SSIS menu you will get Logging.

Click on Logging

Configure SSIS Logs windows will be open.

Select the provider type is SSIS log provider for Text files.

Click on Add button you will get below window.

If you have the connection then configure otherwise we need to create a new configuration.

Create the connection.

Now Text file logging connection created.

Now we need Log the information on Text file.

In Container section

We can log the information on three labels

Ø  Package label

Ø  Container (loop container or sequential) label

Ø  Individual task label

Select the section and go to the details section you will get the list of events on which you want to log.

Select the event and click ok.

You are seeing the number of events. You can log according to your requirement.   Now you run the package. Log will be created.

Open the log in Note pad you will get the information like below.

List of Important Events

Event	Description
OnError	When an error occurs
OnExecutionStatusChange	When execution status has been changed
OnPostexecution	After completing the package or task
OnPreExecution	Before execution
OnPreValidation	Before execution validate the configuration and connection
OnPostValidation	Execute after validation has been completed.
OnProgress	Measurable progress of the executable
OnTaskfailed	When task is failed
OnInformation	Get Information
OnQueryCancel	When we stop the running
OnVariablevaluechange	When the value of the variable is changed
Onwarning	When a warning occurs.

*Events- Perform some jobs based on some action.

There are below event which is mostly used.

Ø  OnError

Ø  OnPreExecution

Ø  OnPostExecution

Ø  OnProgress

Ø  OnTaskFailed

SQL Server Logging

I am going to implement the Sql server logging on the above developed package.

Right click on the package and Click on Logging

Configure SSIS Logs windows will be open.

Select the provider type is SSIS log provider for SQL Server.

Click on add button.

Configure the database where you want to store the logging information.

Click on New a connection manager window will be open.

Write the server name where you want to store the log information.

Select the Authentication mode either window authentication sql server.

Select the Database.

Test the connection.

Now Click on Ok.

Select the list of events which you want to logging.

Now run the package.

Now open the Sql server

Select the database in which you store the log information.

In system tables you will get the dbo.sysssislog.

You can see the log information.

SELECT [id]       ,[event]       ,[computer]       ,[operator]       ,[source]       ,[sourceid]       ,[executionid]       ,[starttime]       ,[endtime]       ,[datacode]       ,[databytes]       ,[message]   FROM [TestSSISDB].[dbo].[sysssislog]

Data code

Code	Description
0	Success
1	Failure
2	Completion
3	canceled

XML File Logging

 I am implementing XML file logging in above package.

Click on logging.

Select the provider type is SSIS log provider for XML files.

Click on Ok button.

In configuration create the new connection. File Connection Manager Editor will be open. Create new connection.

Give the file path where you want to store the Xml file.

Click Ok.

Connection has been created.

Now we need to select the event which we want to logging. We can log the event on package label, container label or individual task label.

Xml file logging has been created.

Run the package.

XML log has been created.

Open it.

Sql server Profiler logging

In this method we are storing the SSIS package log in trace file (.trc).  Sql server profiler is a rich interface to create and manage trace and replay trace result.

Let’s learn how to implement Sql server profiler logging in SSIS package

Click On Logging

Select provider type is SSIS log provider for SQL Server Profiler.

Click on Add

In Configuration, create new connection.

Create trace file. I have created trace file (SqlServerProfiler.trc).

Click Ok.

Now connection has been created.

Now we need to select the event which we want to logging. We can log the event on package label, container label or individual task label.

Click ok.

Now SQL Server profiler logging has been created.

Run the SSIS package.

Trace file has been created.

Now for seeing thin file we need to open sql server profiler.

There are two way to open SQL Server profiler.

First Way

Go to Start -> Microsoft Sql server(2008,2012,2014 etc) -> Performance Tools -> SQL server Profiler

Click on SQL Server Profiler

In this window Go to -> File -> Open -> Trace File

Click on Trace File

Select the Trace file and open it

Trace File content.

Second way to open the SQL Server profiler

Open the Sql Server Management Studio (SSMS)

Go to Tools -> SQL Server Profiler

Click on the SQL Server Profiler

Now connect to the server.

Once connect to the server you can open the trace file and see the result.

Windows Event log

Whenever we run the SSIS Package log will be written in same Machine.  

Click on Logging

Select SSIS log provider for Windows Event log

Click on Add

In Configuration section we are not seeing any connection. Previous we saw the connection. For Window event log no need to connection because it log the information Event Viewer. Where we are running this package in that machine the log is written on Their Event Viewer. So we do  no need to create connection.

Now we need to select the event which we want to logging. We can log the event on package label, container label or individual task label.

Windows Event log is created.

Now we are running the SSIS package.

To see the Windows Event log Go to start and in search tab write Event Viewer. In program you will see the Event viewer.

Click on Event Viewer

Click on Window Logs

Click on the Application

In Application Click on the Information you will get the log information.

Error is also logged on Event viewer.

Click on Details you can view the log in Friendly View.

You can also see the log in XML view.

Custom logging

Suppose we want to log the information like how much data we have loaded in this execution and who loaded that that or other such type of information. For logging (storing) such type of information there is no Built in application. We need to create a table according to our need and logged that information in this table.

Download : Click Here

Hope this article helps you. Please leave comment to make better.