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.