Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

An Introduction to Integration Services log providers

By Stan Kulp,

To demonstrate SQL Server Integration Services' logging features, create a new SSIS package by clicking "Create: Project" from the opening screen of Microsoft Visual Studio, selecting the "Integration Services Project" template, and entering the name "LoggingDemo."

Click the "OK" button to create the project.

A default SSIS package named "Package.dtsx" has been created and opened by default. Drag and drop the "Script Task" component from the "Toolbox" panel to the "Control Flow" panel and save the package.

At this point we have an SSIS package with a dummy script task that does nothing, but that's all we need for our purposes.

Select "SSIS - Logging" from the Visual Studio main menu.

Click on the "Provider type:" drop-down list to view the five available log providers.

We are going to create and configure connections for all five types of providers, then run the package and see what the results are in all five destinations.

Here is a summary of what the providers do and how we are going to use them in the demo.

SSIS log provider for SQL Server - Writes log entries for events to a SQL Server database. In this case, we are going to create and connect to a database named "Logging." By default, the event data will be written to a system table named "dbo.sysssislog."

SSIS log provider for SQL Server Profiler - Writes SQL Server traces that may be captured in SQL Server Profiler. For this demo we have created a folder named "C:\SSIS_Logging\" where the trace file named "SSIS_Execution.trc" will be created by the SSIS package when we configure it through the "Configure SSIS Logs" screen.

SSIS log provider for Text Files - Writes log entries for events to a CSV file. The text file "C:\SSIS_Logging\SSIS_Execution.csv" will be created by the SSIS package when we configure it through the "Configure SSIS Logs" screen.

SSIS log provider for Windows Event Log - Writes log entries for events to the" Windows Event Log." Windows events can be viewed through the "Control Panel - Administrative Tools - View Event Logs" screen.

SSIS log provider for XML Files - Writes log entries for events to an XML file.The XML file "C:\SSIS_Logging\SSIS_Execution.xml" will be created by the SSIS package when we configure it through the "Configure SSIS Logs" screen.

Below is a screen shot of the three log files that we will be creating.

Click on the "Details" tab of the "Configure SSIS Logs: Package" screen.

Click on the "Package" check box to activate package-level logging.

If you do not check anything in the "Details" tab, the only events logged will be the package-level "PackageStart" and "PackageEnd" events.

For the purposes of our demo, check the "OnError," "OnPostExecute," "OnPreExecute", "OnTaskFailed," and "OnWarning" checkboxes. These  events will be captured to all five log providers.

Next click on the "Providers and Logs" tab.

Add the "SSIS log provider for Windows Event Log" to the package by selecting it from the "Provider type:" drop-down list and clicking the "Add" button.

Click the "SSIS log provider for Windows Event log" check box to activate it. There is no configuration requred for the Windows Event log provider.

Next add the "SSIS log provider for Text files" by selecting it from the drop down list and clicking the "Add" button.

Click the "SSIS log provider for Text files" check box to activate it. Configure a new File Connection Manager pointing to the file "C:\SSIS_Logging\SSIS_Execution.csv."

Next add the "SSIS log provider for XML files" by selecting it from the drop down list and clicking the "Add" button.

Click the "SSIS log provider for XML files" check box to activate it. Configure a new File Connection Manager pointing to the file "C:\SSIS_Logging\SSIS_Execution.xml."

Next add the "SSIS log provider for SQL Server" by selecting it from the drop down list and clicking the "Add" button.

Click the "SSIS log provider for SQL Server" check box to activate it.

SInce we want our SQL Server log provider to write to a database named "Logging," we must now create the "Logging" database.

Execute the following T-SQL statement in SQL Server Management Studio.

USE master
GO
CREATE DATABASE Logging

Click on the "SSIS log provider for SQL Server" "Configuration" drop-down list and select "New Connection."

Click on the "New" button of the "Configure OLE DB Connection Manager" panel.

Configure the server name and database name to connect to the Logging database we just created, then click the "OK" button.

Select the new connection manager in the "Configure OLE DB Connection Manager" and click the "OK" button.

The SQL Server log provider can now talk to the Logging database.

Next add the "SSIS log provider for SQL Server Profiler" by selecting it from the drop down list and clicking the "Add" button.

Click the "SSIS log provider for SQL Server Profiler" check box to activate it. Configure a new File Connection Manager pointing to the file "C:\SSIS_Logging\SSIS_Execution.trc."

The screen shot below shows all five log providers.

At this point the demo package is configured to write to all five log providers upon execution, but there are only four connection managers in the screen shot below because the Windows Event log provider does not require an explicit connection.

Execute the SSIS package to generate logging data for all five providers.

Log provider output

All five providers generate the same basic information:

  1. Package Start
  2. Package PreExecute
  3. Script Task PreExecute
  4. Script Task PostExecute
  5. Package PostExecute
  6. Package End

Following is the actual output of the five providers.

1. Output of log provider for SQL Server

2. Output of log provider for SQL Server Profiler

3. Output of log provider for Windows Event Log

4. Output of log provider for Text files

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
PackageStart,XW4100-9,xw4100-9\User,LoggingDemo,{F8168E5D-039A-4774-B9BE-DEC056C774D0},{217FA179-0736-4EA9-93C0-67DD60718F5F},11/12/2011 4:41:32 PM,11/12/2011 4:41:32 PM,0,0x,Beginning of package execution.

OnPreExecute,XW4100-9,xw4100-9\User,LoggingDemo,{F8168E5D-039A-4774-B9BE-DEC056C774D0},{217FA179-0736-4EA9-93C0-67DD60718F5F},11/12/2011 4:41:32 PM,11/12/2011 4:41:32 PM,0,0x,(null)
OnPreExecute,XW4100-9,xw4100-9\User,Script Task,{F17A0CC5-3014-471F-8F8F-316A7C80E780},{217FA179-0736-4EA9-93C0-67DD60718F5F},11/12/2011 4:41:32 PM,11/12/2011 4:41:32 PM,0,0x,(null)
OnPostExecute,XW4100-9,xw4100-9\User,Script Task,{F17A0CC5-3014-471F-8F8F-316A7C80E780},{217FA179-0736-4EA9-93C0-67DD60718F5F},11/12/2011 4:41:32 PM,11/12/2011 4:41:32 PM,0,0x,(null)
OnPostExecute,XW4100-9,xw4100-9\User,LoggingDemo,{F8168E5D-039A-4774-B9BE-DEC056C774D0},{217FA179-0736-4EA9-93C0-67DD60718F5F},11/12/2011 4:41:32 PM,11/12/2011 4:41:32 PM,0,0x,(null)
PackageEnd,XW4100-9,xw4100-9\User,LoggingDemo,{F8168E5D-039A-4774-B9BE-DEC056C774D0},{217FA179-0736-4EA9-93C0-67DD60718F5F},11/12/2011 4:41:32 PM,11/12/2011 4:41:32 PM,0,0x,End of package execution.

5. Output of log provider for XML files

<?xml version="1.0"?>
<dtslogs>
    <dtslog>
    </dtslog>

    <dtslog>
    </dtslog>

    <dtslog>
<record><event>PackageStart</event><message>Beginning of package execution.
</message><computer>XW4100-9</computer><operator>xw4100-9\User</operator><source>LoggingDemo</source><sourceid>{F8168E5D-039A-4774-B9BE-DEC056C774D0}</sourceid><executionid>{217FA179-0736-4EA9-93C0-67DD60718F5F}</executionid><starttime>11/12/2011 4:41:32 PM</starttime><endtime>11/12/2011 4:41:32 PM</endtime><datacode>0</datacode><databytes>0x</databytes></record><record><event>OnPreExecute</event><message></message><computer>XW4100-9</computer><operator>xw4100-9\User</operator><source>LoggingDemo</source><sourceid>{F8168E5D-039A-4774-B9BE-DEC056C774D0}</sourceid><executionid>{217FA179-0736-4EA9-93C0-67DD60718F5F}</executionid><starttime>11/12/2011 4:41:32 PM</starttime><endtime>11/12/2011 4:41:32 PM</endtime><datacode>0</datacode><databytes>0x</databytes></record><record><event>OnPreExecute</event><message></message><computer>XW4100-9</computer><operator>xw4100-9\User</operator><source>Script Task</source><sourceid>{F17A0CC5-3014-471F-8F8F-316A7C80E780}</sourceid><executionid>{217FA179-0736-4EA9-93C0-67DD60718F5F}</executionid><starttime>11/12/2011 4:41:32 PM</starttime><endtime>11/12/2011 4:41:32 PM</endtime><datacode>0</datacode><databytes>0x</databytes></record><record><event>OnPostExecute</event><message></message><computer>XW4100-9</computer><operator>xw4100-9\User</operator><source>Script Task</source><sourceid>{F17A0CC5-3014-471F-8F8F-316A7C80E780}</sourceid><executionid>{217FA179-0736-4EA9-93C0-67DD60718F5F}</executionid><starttime>11/12/2011 4:41:32 PM</starttime><endtime>11/12/2011 4:41:32 PM</endtime><datacode>0</datacode><databytes>0x</databytes></record><record><event>OnPostExecute</event><message></message><computer>XW4100-9</computer><operator>xw4100-9\User</operator><source>LoggingDemo</source><sourceid>{F8168E5D-039A-4774-B9BE-DEC056C774D0}</sourceid><executionid>{217FA179-0736-4EA9-93C0-67DD60718F5F}</executionid><starttime>11/12/2011 4:41:32 PM</starttime><endtime>11/12/2011 4:41:32 PM</endtime><datacode>0</datacode><databytes>0x</databytes></record><record><event>PackageEnd</event><message>End of package execution.
</message><computer>XW4100-9</computer><operator>xw4100-9\User</operator><source>LoggingDemo</source><sourceid>{F8168E5D-039A-4774-B9BE-DEC056C774D0}</sourceid><executionid>{217FA179-0736-4EA9-93C0-67DD60718F5F}</executionid><starttime>11/12/2011 4:41:32 PM</starttime><endtime>11/12/2011 4:41:32 PM</endtime><datacode>0</datacode><databytes>0x</databytes></record>    </dtslog>
</dtslogs>

Conclusion

SQL Server Integration Services features five types of log providers for package-execution event logging. Each of these providers generates the same basic information but sends it to different destinations. The choice of which one to use depends upon what you want to do with the logging information.

Total article views: 3317 | Views in the last 30 days: 8
 
Related Articles
ARTICLE

On Error () Logging by Using Event Handlers

Use of event handlers provided in SSIS package to handle runtime errors while the package is running...

FORUM

Can i change Provider in package by Execute Package Utility

Can i change Provider in package by Execute Package Utility

FORUM

Click Event of a Textbox

Want to change the color of the textbox on clicking

FORUM

SSIS Package Event Handlers Executables Disappear

When oppening the package executables on an OnError event handler are hidden but they work in the ba...

BLOG

Undocumented Capabilities of Extended Event Objects

Undocumented Capabilities of Extended Event Objects The extended event objects (objects exposed by ...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones