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:
- Package Start
- Package PreExecute
- Script Task PreExecute
- Script Task PostExecute
- Package PostExecute
- 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.
