CODEWITHSUNDEEP
sql-serverssis
Greg
Greg

Reputation: 47134

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

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,

Upvotes: 7

Views: 12396

Answers (6)

Burkhard Lau
Burkhard Lau

Reputation: 51

In case you want to monitor the execution during running the project, you will have to use a more sophisticated query like:

SELECT MIN(A.ID) AS ID,
 A.Source
, MIN(A.StartTime) AS StartTime
,  case when MAX(B.endtime) IS NULL then null else round((cast(MAX(B.endtime) as float) - cast(MIN(A.starttime) as float))*24*60*60, 0) end AS Seconds
, C.Message
FROM (
SELECT ID, Source, StartTime, ExecutionID
FROM SysSSiSLog
WHERE Event = 'PackageStart' 
) A
LEFT OUTER JOIN (
SELECT Source, EndTime, ExecutionID
FROM SysSSiSLog
WHERE Event = 'PackageEnd' 
) B 
ON A.Source = B.Source AND A.ExecutionID = B.ExecutionID
LEFT OUTER JOIN (
SELECT distinct Source, EndTime, ExecutionID, Message
FROM SysSSiSLog
WHERE Event = 'OnError'
) C 
ON A.Source = C.Source AND A.ExecutionID = C.ExecutionID
WHERE A.ID >= (SELECT MAX(ID) FROM SysSSiSLog WHERE Source = 'Main' AND Event = 'PackageStart')
GROUP BY A.Source, A.ExecutionID, C.Message
ORDER BY min(A.ID) Asc

Upvotes: 0

Andrew Tasi
Andrew Tasi

Reputation: 149

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.

Upvotes: 3

sabyasm
sabyasm

Reputation: 56

The column "sourceid" would be the same as your SSIS package GUID for the events

  • PackageStart
  • PackageEnd

As said above - the executionid is the guid of the particular instance of the run.

You may want to enable "OnError" event handler in order to produce the package that didn't fail.

To generate the report what you can do :

join msdb.[dbo].[sysdtspackages] and dbo.sysssislog table on id = sourceid. The packages that failed will have OnError entry in sysssislog table from which you can infer the status.

-- Please mark if this answers your question

Upvotes: 1

William Salzman
William Salzman

Reputation: 6446

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.

Upvotes: 0

Denis Valeev
Denis Valeev

Reputation: 6015

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

Upvotes: 2

SQLMenace
SQLMenace

Reputation: 135111

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.

Upvotes: 1

Related Questions