SSIS - How to Identify which package a row in the log table is referring to?

Question

I have multiple SSIS integration packages logging to a database. They all write to the table sysssislog.

I want a stored procedure to be able to return the success of the last run of a selected package.

How do I identify a package in sysssislog? The executionid field would seem to work, but it seems like it's changing values on mosts runs of the same package (sometimes it stays the same). Is there some way to know which package a log entry is coming from?

Structure of sysssislog for reference:

CREATE TABLE [dbo].[sysssislog](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [event] [sysname] NOT NULL,
    [computer] [nvarchar](128) NOT NULL,
    [operator] [nvarchar](128) NOT NULL,
    [source] [nvarchar](1024) NOT NULL,
    [sourceid] [uniqueidentifier] NOT NULL,
    [executionid] [uniqueidentifier] NOT NULL,
    [starttime] [datetime] NOT NULL,
    [endtime] [datetime] NOT NULL,
    [datacode] [int] NOT NULL,
    [databytes] [image] NULL,
    [message] [nvarchar](2048) NOT NULL,

Answer 1

Take a look if this helps you, from Books On Line

source nvarchar

The name of the executable, in the package, that generated the logging entry.

sourceid uniqueidentifier

The GUID of the executable in the package that generated the logging entry.

Answer 2

Here's a nice view candidate to take a look at the history of execution of all the packages in your SSIS, you can also see how long a package was running in minutes:

select 
 source [PackageName], s.executionid
, min(s.starttime) StartTime
, max(s.endtime) EndTime
, (cast(max(s.endtime) as float) - cast(min(s.starttime) as float))*24*60 DurationInMinutes
from dbo.sysssislog as s
where event in ('PackageStart', 'PackageEnd')
--and source = 'foobar'
group by s.source, s.executionid
order by max(s.endtime) desc

Answer 3

Source ID column where the event is "Package Start" identifies the package Name. The Execution ID ties in all of the related rows for that instance of your package run.

Source ID can be tied back to your development of your package by opening your package and looking at the ID field in your package level properties. This GUID matches your package level source ID column in the log. Each object in your package will also have its own GUID and these can be seen in the log.