Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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...>

 

Img1

After selecting <Logging> you will be faced with the screen below:

Img2

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

Img3

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.


Comments

Posted by Jason Brimhall on 12 July 2010

Thanks Gethyn.  Being able to log SSIS packages is a very useful but often overlooked task.

Leave a Comment

Please register or log in to leave a comment.