Geek Answers Handbook

SSIS - How to determine which package a row in a log table belongs to?

I have several SSIS integration packages registering in a database. They all write to the sysssislog table.

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

How to define a package in sysssislog? The executable file field seems to work, but it looks like it changes values ​​on most runs of the same package (sometimes it remains unchanged). Is there any way to find out which package the journal entry comes from?

The sysssislog structure 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,
+5
source share
5 answers

, SSIS, , :

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
+2

, , Books On Line

nvarchar

, .

GUID , .

+1

"sourceid" , GUID SSIS

  • PackageStart
  • PackageEnd

, .

, "OnError" , , .

, :

msdb. [dbo]. [sysdtspackages] dbo.sysssislog id = sourceid. , , OnError sysssislog, .

- , , .

+1

, SSIS. , ExecutionID , , SSIS SQL Server.

SQL Server 2008, SSIS "sysssislog", :

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

:

  • convert .
  • , SQL Server 2005.
  • , "SSIS_Log_View"

, .

+1

, " " . .

The source identifier can be tied to your development of your package by opening your package and looking at the ID field in your package level properties. This GUID corresponds to the source packet level identifier column in the log. Each object in your package will also have its own GUID, and this can be seen in the log.

0
source

More articles:


All Articles