Monday, 12 July 2010

SSIS - Enabling Logging

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.

1 comment:

  1. Weather Measuring Instruments are using now in crime detecting sectors. Using it with I-Pads has brought a revolution in their detecting machines and output. It has made their work easier.


Featured post

OUT OF SUPPORT: SQL Server 2008 and SQL Server 2008 R2

Welcome to this post, on SQL Server 2008 and 2008 R2, both of these versions of SQL server will go out of  extended support with Microsoft t...