SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Compute Package run time from log table		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

katalystguy
Starting Member

20 Posts
Posted - 05/21/2012 :  17:33:29  Show Profile  Reply with Quote
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

India
47173 Posts
Posted - 05/21/2012 :  18:38:33  Show Profile  Reply with Quote 

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]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

katalystguy
Starting Member

20 Posts
Posted - 05/22/2012 :  06:37:59  Show Profile  Reply with Quote
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]
Go to Top of Page

katalystguy
Starting Member

20 Posts
Posted - 05/22/2012 :  06:38:45  Show Profile  Reply with Quote
meant to say it is returning those as well as the correct rows.
Go to Top of Page

katalystguy
Starting Member

20 Posts
Posted - 05/22/2012 :  06:57:19  Show Profile  Reply with Quote
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]
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000