Compute Package run time from log table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Compute Package run time from log table

Author

Topic

katalystguy
Starting Member

20 Posts

Posted - 2012-05-21 : 17:33:29

Hi,

I have an SSIS project with logging enabled to get performance data. For each package in the project various records are wriiten at different milestones. I am only interested in the PackageStart and PackageEnd event records, yes they are seperate records. I have wriiten the following SQL but the inner expression generates an error due to multiple rows returned, which I do not understand, but there you go.

select
s1.[event], s1.[source], s1.[starttime], s1.[endtime], datediff(minute, s1.starttime, (select s2.endtime from sysdtslog90 s2 where s1.source = s2.source and s2.event like '%PackageEnd')) as Duration
from
sysdtslog90 s1
where
s1.[event] Like '%PackageStart'
order by
s1.[starttime]

Cheers.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 18:38:33

[code]
select
s1.[source],
datediff(minute, max(case when [event] like '%PackageStart' then starttime end),
max(case when [event] like '%PackageEnd% ' then endtime end)) as Duration
from sysdtslog90 s1
group by [source]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

katalystguy
Starting Member

20 Posts

Posted - 2012-05-22 : 06:37:59

Hi,

It works, but .... it returns the entries not containing either '%PackageStart' or '%Package End'. Any ideas. Also modified the statement slightly to get it to work, see below:

select
s1.[source],
datediff(minute,
max(case when [event] like '%PackageStart' then starttime end),
max(case when [event] like '%PackageEnd' then endtime end)) as Duration
from
sysdtslog90 s1
group by
s1.[source]

katalystguy
Starting Member

20 Posts

Posted - 2012-05-22 : 06:38:45

meant to say it is returning those as well as the correct rows.

katalystguy
Starting Member

20 Posts

Posted - 2012-05-22 : 06:57:19

Ok, put in a where clause to resolve that issue, see below. Next issue is I need the resulting rows sorted by [starttime], not sure how this would work in a group by statement?

select
[source],
datediff(minute,
max(case when [event] like '%PackageStart' then starttime end),
max(case when [event] like '%PackageEnd' then endtime end)) as Duration
from
sysdtslog90
where
[event] Like '%PackageStart' or [event] like '%PackageEnd'
group by
[source]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources