by Ken Whiteside
DataXperts

How to turn on SSIS Package Logging.

Before we turn on the logging capability, we must setup a database to hold the logs. Sure you could use text files. But if you use a database, you can turn logging on all your SSIS packages regardless of the servers they are running on and point them to a single database. The benefit of this is having a central place to check the status of all SSIS jobs running on all servers in your company!

I call my database SSIS_Logs as shown below. The necessary tables will be created automatically. The stored procedures are from a Microsoft Add on that you can download here to create Reporting Services Reports that do a very nice job of parsing these log tables. I look at these reports every morning to check the SSIS packages that ran the night before!

Add a connection to the SSIS_Logs database.

This database will be utilized for tracking the status of all SSIS packages.

All packages will Log to the Same Server with the same database so the reports will display the status of all Jobs ran. Don’t forget to put in the password and click “Save my password”

Turn on Logging inside each of your packages.

Point the SSIS_Logs connection.

Right click inside the package and select Logging.

Check the container, Pick the provider type, and click Add. Make sure it looks like the screen below.

Click the Details Tab

Select what you think you need here. I have found the following give me enough detail without drowning me.

OnError, OnPostExecute, OnTaskFailed, OnWarning

View Logging data

You can look at the sysdtslog90 table directly, but it is rather cryptic. Microsoft provides some excellent reports to view them with.

See the tutorial on viewing the SSIS Event Logs.

The screens below are teasers.