August 13, 2013 at 12:25 pm
I have the following SQL that I run for DTS packages to be able to determine how long individual steps in the package took.
USE msdb;
declare @packagename varchar(255)
set @packagename = 'Month_End_Step_2_Primary_Update'
select stepname, starttime, endtime, convert(char(12),(endtime - starttime), 108) as elasped_time, elapsedtime
from msdb..sysdtssteplog
where lineagefull = (select lineagefull
from msdb..sysdtspackagelog
where [name] = @packagename
and logdate = (select max(logdate)
from msdb..sysdtspackagelog
where [name] = @packagename
--and starttime < '2012-08-01 00:00:00.000'
)
)
and stepname not like '%e-mail%'
and stepname not like 'Success email%'
and stepname not like '%Parameter%'
order by stepexecutionid
Here is the results I get:
stepnamestarttimeendtimeelasped_timeelapsedtime
Agency Cross reference SQL7/1/2013 8:51:49.0007/1/2013 8:58:32.0000:06:43403.328
DTSStep_DTSExecuteSQLTask_37/1/2013 8:58:32.0007/1/2013 8:59:14.0000:00:4241.641
DTSStep_DTSExecuteSQLTask_47/1/2013 8:59:14.0007/1/2013 9:00:03.0000:00:4948.734
DTSStep_DTSActiveScriptTask_57/1/2013 8:59:14.0007/1/2013 8:59:14.0000:00:000.031
DTSStep_DTSActiveScriptTask_67/1/2013 9:00:03.0007/1/2013 9:00:03.0000:00:000.031
UpdateRegionCodeRegionTable7/1/2013 9:00:03.0007/1/2013 9:12:35.0000:12:32752.093
DTSStep_DTSActiveScriptTask_127/1/2013 9:12:35.0007/1/2013 9:12:35.0000:00:000.047
Balancing_by_Region7/1/2013 9:12:35.0007/1/2013 9:12:50.0000:00:1515.641
My question is does anyone have something similar I could run to get the same type of results for SSIS packages?
I have SQL to look at the log file created from the SSIS package, it just doesn't put it in the nice format that this is with start and stop times on one row.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 15, 2013 at 7:39 am
Here is some code I've used to try and get this info, it helps cut out some of the clutter, but it still doesn't produce one line per 'step' with start and stop times. It's hard to believe I'm the only one who has tried this.
declare @PackageName varchar(200);
set @PackageName = 'Monthly_Extract_Process'
declare @starttime as datetime;
set @starttime = getdate();
select executionid, sourceid, min(id) as id, min(starttime) as starttime, max(endtime) as endtime
into #work
from DTS_Parameters.dbo.sysssislog
where executionid in (select distinct executionid
from DTS_Parameters.dbo.sysssislog
where source = @PackageName
and starttime = (select max(starttime)
from DTS_Parameters.dbo.sysssislog
where source = @PackageName
and starttime < @starttime
)
and id = (select max(id)
from DTS_Parameters.dbo.sysssislog
where source = @PackageName
and starttime = (select max(starttime)
from DTS_Parameters.dbo.sysssislog
where source = @PackageName
and starttime < @starttime
)
)
)
group by executionid, sourceid
select datacode, message, event, source, l.starttime, l.endtime, l.id, l.sourceid, computer, operator, l.executionid--, databytes
from #work l
inner join DTS_Parameters.dbo.sysssislog r on
l.executionid = r.executionid
and l.sourceid = r.sourceid
and l.id = r.id
order by l.id desc;
drop table #work
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply