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

Like the original poster, I wanted to see the name of my package in front of all of my source names when going through my SSIS log. In reading William's response, I realized the ExecutionID could be leveraged to do exactly that, at least when using the SSIS log provider for SQL Server.

If you're using SQL Server 2008 and your SSIS logging table uses the standard name of "sysssislog", then try this query:

SELECT s1.id, s1.operator, s1.event, s2.source package_name, s1.source,
    CONVERT(varchar, s1.starttime, 120) AS starttime, s1.message, s1.datacode
FROM dbo.sysssislog AS s1 LEFT OUTER JOIN
       dbo.sysssislog AS s2 ON s1.executionid = s2.executionid
WHERE s2.event = 'PackageStart'
ORDER BY s1.id

Notes:

• The convert statement trims off the fractional seconds.
• Adjust the table name if you're using SQL Server 2005.
• I used the query to create a view I called "SSIS_Log_View"

I hope that helps.