How to get the runtimes of individual steps of an SSIS package

Open up the SSIS package which you want to add logging to and follow these steps:

·         Right click anywhere in the design screen and click on “Logging”

·         In the “Configure SSIS Logs: PROJECT NAME”  on the “Providers and Logs” tab select “SSIS Log Provider for SQL Server” and Click “Add”

·         In the newly added item in the list box choose or add a new configuration for the log

·         In the Details tab select the “OnPostExecute” and “OnPreExecute” events.

·         Click OK to save your changes

The next time you run your SSIS package a new table named sysssislog will be created.  By running the query below you will be able to view the start and end times of the different pieces of your SSIS package

declare @start_of_run as smalldatetime

set @start_of_run = '01/02/2013 14:47'

select pre.source, pre.starttime, post.endtime, isnull(cast(DATEDIFF(second, pre.starttime, post.endtime) as varchar),'In Progress') as run_time_seconds

from sysssislog pre

left outer join sysssislog post on pre.source = post.source and pre.executionid = post.executionid and post.event ='onpostexecute'

where pre.starttime > @start_of_run

and pre.event = 'onpreexecute'

order by pre.starttime