Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm writing a logging process that will be stored in tables so that the information can be easily referenced and reported. Most of the structure already exists in the system tables for the SQL jobs (msdb.dbo.sysjobs, msdb.dbo.sysjobsteps, etc); however, I want more detail for each execution of a package, so I'm implementing a logging system that will keep track of each task in the package. Basically, for each task (Data Flow, Execute SQL Task, etc), a detailed, custom description of what has occurred will be stored. More specifically, in the event of a failure, the name of the task that failed and the reason for the failure can be quickly determined.

Tying each log record to the ID of the job and step is not an issue, those already exist and are easily captured. The instance_id generated in msdb.dbo.sysjobhistory, however, does not exist until after the execution of the step. Getting that ID doesn't seem possible during the execution of the package.

By the way, I tested when a record is inserted into msdb.dbo.sysjobhistory by creating a job and running a single step with the following T-SQL code:

WAITFOR DELAY '00:01:00'

The goal was to see when the instance_id is generated and inserted into msdb.dbo.sysjobhistory, which occurred after the step had completed.

share|improve this question
    
Please complete your last sentence :-). Can you use tokens during the job execution to get your step id? –  Marian Jan 24 '13 at 21:40
    
@Marian Thanks, I'm not sure what happened to the rest of it when I submitted it. As for tokens, I can't see how that would be possible to do during the execution of the SSIS package since the instance_id record does not exist in the table. Whether it exists somewhere else at that point as a token, I do not know. –  Davenport Jan 24 '13 at 21:44

2 Answers 2

up vote 1 down vote accepted

To answer directly to the question in the title: No. It's already specified in the documentation of sysjobhistory.

Contains information about the execution of scheduled jobs by SQL Server Agent. This table is stored in the msdb database.

Note: Data is updated only after the jobstep completes.

Regarding the other details, Peter's idea might work, though I don't understand why you need the step history id during the step execution itself, as you don't really know the error message, the step details, the duration, as it didn't finish the execution yet.

share|improve this answer

If you create dummy steps between each real step, will that work?

So if step1 is Start Import, ans step2 is Run Import, you'll see step1 completed and you'll know step2 is running.

Alternatively, just insert all the steps into your own own audit table?

share|improve this answer
    
I was planning to create my own table, I want to create a one-to-many relationship between sysjobhistory and my table (let's call it TaskLog) with instance_id. The problem is that instance_id is generated after the step is complete. –  Davenport Jan 24 '13 at 20:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.