How to enable and use SSIS logging in a SQL server table, part I

This article describes how to enable loggin in SSIS to a SQL server table and how to log the package start and stop time.

Prerequisites

To log the package start and stop time, and various other events and data, I will use a SQL server database, to which my SSIS account has administrative rights to create a table and insert records into it. The logging procedure will create the table dbo.sysssislog when not available. Then it will add records to it as described below.

Walkthrough

• Create an OLE DB connection called MyLogging
• In the VS2008 menu choose SSIS / Logging… For ‘Provider type’ select  SSIS log provider for SQL server and click Add.
• The Log provider is shown in the ‘Select the logs to use for the container’ list.
• In the ‘Configuration’ column select the connection MyLogging from the dropdown.
• In the ‘Containers’ treeview click the checkbox near the top Container.  in the ‘Select the logs to use for the container’ list click the checkbox near the SSIS log provider. Without further settings, this is enough to generate a Package start and Package end log record. The recording will occur in the table called dbo.sysssislog
•