Have you ever had a scheduled run of one of your SSIS packages fail? Have you ever wished you had a little more information available to you when troubleshooting? SSIS has a neat little feature that you can enable called 'Logging' and it does pretty much what it says on the tin. It logs the execution of your SSIS packages, you have a variety of logging options to choose from including logging to a SQL Server database itself, and if the worst happens and the execution fails you can gain some useful information regarding the reasons behind the failure.
Setting Up Logging
You can enable logging within your package. Open the SSIS package in BIDS (Business Intelligence Design Studio)
My SSIS package has two control flow tasks 1. Execute SQL Task which is used to truncate a staging table and 2. A Data Flow task which copies some data from the Sales Header table in the Adventure works database to a staging database.
Right click on some open space on the control flow tab and select <Logging...>
After selecting <Logging> you will be faced with the screen below:
I want to enable logging to capture the execution of the whole package so I enabled the tick box called package (the root container) which automatically selected the other two control flow tasks.
On the <Providers and Logs> tab I selected a provider type of <SSIS log provider for SQL Server> and selected <Add>. In the "Select the log to use for the container" section (My container in this case is the whole package) I selected the tick box for my log provider. In the configuration column (I had created a separate database to hold the log table, but you could use any database if you wish) I setup a new connection to point at the logging database.
On the details tab I selected the following events:
- OnError
- OnPostexecute
- OnPreExecute
- OnWarning
There is a whole bunch of events to choose from
Click <OK> and you have configured logging.
I then run my package.
And I can see what has been logged to the SQL Server table by running this simple query:
select * from dbo.sysssislog
The results contain a whole bunch of useful information including any messages returned including errors and start and end time of specific tasks.
