Monday 2 November 2015

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.


Popular Posts