SSIS Package Logging

Logging is important for SSIS package debugging and auditing. Here I will be describing the process of logging in SSIS packages.

Logging is the process to capture run-time information about a package, helping you to audit and troubleshoot a package every time it is run. SSIS provides log providers that you can use to implement logging in packages. SSIS provide five different types of log providers,

·         SSIS log provider for SQL Server

·         SSIS log provider for Windows Event Log

·         SSIS log provider for XML Files

·         SSIS log provider for Text Files

·         SSIS log provider for SQL Server Profiler

Here I will be using SSIS log provider for SQL Server, which will create a table in the SQL Server that gives log details of the package and tasks within the package.

The logging option can be seen by right clicking anywhere on the design surface of the SSIS package or in the SSIS menu. When you click on the logging option, the following window pops up Fig 1. There will be SSIS package and the various tasks in SSIS package on the left. On the right hand side within the window you have two tabs Providers and Logs, Details. When you choose the Provider type there are different types of providers available:

 Fig 1

·         SSIS log provider for SQL Server

·         SSIS log provider for Windows Event Log

·         SSIS log provider for XML Files

·         SSIS log provider for Text Files

·         SSIS log provider for SQL Server Profiler

Select SSIS log provider for SQL Server, once this is done check the box next to the option SSIS log provider for SQL Server, this gives us the option to create the connection manager or use existing connection manager, then  choose the database connection where you want to store the log records. When SSIS package runs it creates a table called SYSDTSLOG90. In the details tab, choose the Events that need to be tracked, look at the Fig 2 below for reference.

Fig 2

Click the save button will save the logging settings to an xml file, which you can reuse the for other packages.

Once this is done, click the OK button, Logging is now enabled for the SSIS package. Once the package is executed, look for the table SYSDTSLOG90 in the database that you gave in the connection manager. The SYSDTSLOG90 table has columns called source (contains the name of each task which is executed), message which contains more detailed message about the task, event (which contains the event that were chosen to be audited). The table also has columns called start time and end time which indicates the start and end time of each task. These SYSDTSLOG90 is described below. 

Table Name	Field	Field Type	Primary / Unique  / Foreign Key	Description & Purpose
SYSDTSLOG90				This table Contains one row for each logging entry that is generated by packages or their tasks and containers at run time
	Id	int	PK(NOT NULL)	The unique identifier of the logging entry
	Event	sysname	NOT NULL	The name of the event that generated the logging entry
	Computer	nvarchar(128)	NOT NULL	The computer on which the package ran when the logging entry was generated
	Operator	nvarchar(128)	NOT NULL	The user name of the person who ran the package that generated the logging entry
	Source	nvarchar(1024)	NOT NULL	The name of the container or task in which the log event occurred
	Sourceid	uniqueidentifier	NOT NULL	The GUID of the package; the For Loop, Foreach Loop, or Sequence container; or the task in which the log event occurred
	Executionid	uniqueidentifier	NOT NULL	The GUID of the execution instance of the executable that generated the logging entry
	Starttime	datetime	NOT NULL	The time at which the container or task starts to run
	Endtime	datetime	NOT NULL	The time at which the container or task stops to run
	Datacode	int	NOT NULL	An An optional integer value that typically contains a value from the DTSExecResult enumeration that indicates the result of running the container or task: 0 - Success 1 - Failure 2 - Completed 3 - Canceled
	Databytes	image	NULL	A byte array that identifies a return value
	Message	nvarchar(2048)	NOT NULL	A description of the event and the information associated with the event