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.
