January 20, 2011 at 3:44 pm
Hello,
I have gotten pretty comfortable with DTS logging and the associated tables. Now we are getting more into SSIS and I'd like to duplicate the queries I use to monitor DTS. My first query would be to view all packages that have or are running for a date range. In DTS/SQL2K my query is this:
select * from sysdtspackagelog where starttime > @dt order by starttime desc
What is nice about the above is that the endtime is null or empty if the package is still running and it's all in the same record as the starttime. As far as I can tell I can't get the same on one line in SSIS. I did come up with the query below for SSIS/SQL2K5:
SELECT
*
FROM
msdb.dbo.sysdtslog90 AS a LEFT JOIN msdb.dbo.sysdtslog90 AS b ON
a.executionid=b.executionid AND
b.[event] = 'PackageEnd'
WHERE
a.[event] = 'PackageStart' AND
a.starttime >= @dt
ORDER BY
a.starttime
It's not too bad, but definitely not as nice as DTS/SQL2K. So my next step was to replicate the query below which lists out individual step info for the DTS, again logging start and end time in the same record:
select * from sysdtssteplog order by starttime
It looks like the individual task logging is lumped into the sysdtslog90 table along with the package logging. I can't figure out how to get the task start and end times logged. I've tried logging onProgress and onInformation which produce alot of unneeded information, but not the start and end times.
So I guess I'd like to know is first that my query for package logging is the easiest solution. Second would be how to log individual task times in general and from there how to get the start and end time in one record per task.
Thanks,
Gunner
January 21, 2011 at 3:15 am
This was removed by the editor as SPAM
January 21, 2011 at 7:53 am
Thanks for the reply. My hope is that I wouldn't have to create custom logging for something so simple and that was part of DTS/SQL2K. Also if I did create this custom component I'd need it for a lot of different tasks. Would it be re-usable and would it I be able to use it for all types of tasks?
January 21, 2011 at 9:37 am
I think I figured it out. Not sure how I missed this, but it looks like the OnPreExecute and OnPostExecute are the events I need. It isn't perfect, but it is close to what I need. I see Stewart also mentions these event, but for some reason another post made it click. Here is the query:
SELECT
*
FROM
msdb.dbo.sysdtslog90 AS a LEFT JOIN msdb.dbo.sysdtslog90 AS b ON
a.sourceid=b.sourceid AND
a.executionid=b.executionid AND
b.[event] = 'OnPostExecute'
WHERE
a.[event] = 'OnPreExecute' AND
a.starttime >= @dt
ORDER BY
a.starttime
It looks like this matches what is reported on the SSIS Execution Results tab as time elapsed. One little issue is it also logs this for the entire package which is a duplicate of the PackageStart/PackageEnd events. Not a big deal and I think you could remedy this by individually specifying the OnPre/PostExecute for each task and take it off the package's events. But for me having a couple extra lines rows in the table is better than have to individually specify logging for each task in a package.
I still have some work to do to replicate my DTS logging queries, but it looks like all the data is there. It's just lumped together. It's funny because I've looked into this multiple times and I finally decided to post a thread. As soon as I do I find what I'm looking for 🙂 Oh well at least it's figured out.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply