SSIS logging is configured on a package-by-package basis.  SSIS provides a flexible event logging environment that allows developers to configure: the container or scope to log (generally the entire package), the type of events to log, and the log provider type (SQL Server, Windows Event Log, Text Files, SQL Server Profiler or XML Files).

Whenever I set up logging in an SSIS package, I always log events at the package level using the SQL Server log provider.  The type of events I log are: On Error, On Post Execute, On Pre Execute, On Task Failed, and On Warning.

The following is a list of steps to configure package logging as I described above:

Right click on the control flow surface and select ‘Logging’ (or select ‘Logging’ in the SSIS menu)

In the ‘Containers’ section, you select the container or scope for logging.  I always select the package level (to log events in the entire package) by checking the box next to the package name. 

In the ‘Add a new log’ section, select ‘SSIS log provider for SQL Server’ then click ‘Add’.

In the ‘Configuration’ field, select the SQL Server database where you want to store logged events.

Next, click the ‘Details’ tab.  On the details tab you specify the types of events you want to log.  I always select the following: On Error, On Post Execute, On Pre Execute, On Task Failed, and On Warning.

• On Error – Writes a log entry when an error occurs.
• On Post Execute – Writes a log entry immediately after the executable has finished running.
• On Pre Execute  – Writes a log entry immediately before the executable runs.
• On Task Failed – Writes a log entry when a task fails.
• On Warning – Writes a log entry when a warning occurs.

When finished, click ‘OK’. 

You should also create an event handler for each type of event you’re logging.  To create an event handler, click the ‘Event Handlers’ tab.

In the ‘Event Handler’ drop-down, select the appropriate event type.  Then click the link to create an event handler for that specific event.  You need to repeat this process for each type of event (On Error, On Post Execute, On Pre Execute, On Task Failed, and On Warning)

When you’re finished, the list in the ‘Event handler’ drop-down should look like this:

Logged events are stored in the dbo.sysssislog system table in the database you specified while configuring package logging.

comments powered by Disqus