Do anyone know where to find the package log details that can be displayed by
right-clicking a package and choosing "Packages Logs..."?

It would be very useful to be able to query this table or tables.

TIA!

dj

This should help some...

SELECT logdate, a.name, b.stepname, c.DTSStepDescription, b.starttime,
b.endtime, b.elapsedtime, b.progresscount, b.errorcode,
LEFT(b.errordescription,3000), b.stepexecutionid, @syncDate
FROM msdb..sysdtspackagelog a
JOIN msdb..sysdtssteplog b
ON a.lineagefull=b.lineagefull
LEFT JOIN PackageStepXref c ON a.name=c.DTSPackageName AND
b.stepname=c.DTSStepName
WHERE logdate IN (SELECT DISTINCT logdate FROM msdb..sysdtspackagelog
WHERE logdate > DATEADD(HOUR,-12,GETDATE()))
ORDER BY stepexecutionid

dj wrote:

Thanks for the code, but...
I'm getting an error on item "PackageStepXref".
I don't recognize this guy. Care to elaborate?
dj

"Slice" wrote:

I used this as a run-once (disabled ActiveX object) when any objects in
the package are adjusted.

' VB_Populate_Step_XRef
' Notice: adjust connection details in Constants as
' appropriate for correct catalog. Default is
' (local) and metadataDB.
'
' - Creates PackageStepXref table when not exists
' - Creates UPDATE trigger on PackageStepXref when not exists
' - Inserts a row for every step found in this package
'
' Notes: Routine does DELETE existing step details prior to INSERT
'
'************************************************* ***********************
Option Explicit

Const CONNECT_DB = "(local)"
Const CONNECT_CAT = "metadataDB"

Function Main()
Dim oPkg
Set oPkg = DTSGLobalVariables.Parent

Dim sSQL, sTriggerExistsFlag, nStepsInserted, nStepsDeleted

sSQL = "if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PackageStepXref]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1) " _
& "CREATE TABLE dbo.PackageStepXref ( " _
& " DTSPackageName varchar (100) NOT NULL, " _
& " DTSStepName varchar (100) NOT NULL, " _
& " DTSStepDescription varchar (100) NULL, " _
& " create_date datetime NOT NULL CONSTRAINT
DF__DTSPackag__creat__2739D489 DEFAULT (getdate()), " _
& " update_date datetime NULL, " _
& " update_user varchar (256) NULL CONSTRAINT
DF__DTSPackag__updat__282DF8C2 DEFAULT (suser_sname()), " _
& " PackageStepXref_id int IDENTITY (1, 1) NOT NULL, " _
& " CONSTRAINT PK_PackageStepXref PRIMARY KEY CLUSTERED
(PackageStepXref_id), " _
& " CONSTRAINT U_PackageStepXref_DTSPackageName_DTSStepName UNIQUE
NONCLUSTERED (DTSPackageName, DTSStepName)) "
TSQL_Execute_Fetch sSQL

sSQL = "select 'Y' from dbo.sysobjects where name =
'tr_PackageStepXref_update' and xtype = 'TR'"
sTriggerExistsFlag = TSQL_Execute_Fetch(sSQL)
If sTriggerExistsFlag <> "Y" Then
' make the update trigger
sSQL = _
"CREATE TRIGGER tr_PackageStepXref_update ON dbo.PackageStepXref " &
vbNewLine _
& "FOR UPDATE " & vbNewLine _
& "AS " & vbNewLine _
& "UPDATE PackageStepXref " & vbNewLine _
& "SET update_date = getdate(), update_user = suser_sname() " &
vbNewLine _
& "FROM PackageStepXref a " & vbNewLine _
& "JOIN INSERTED b ON a.PackageStepXref_id=b.PackageStepXref_id"
TSQL_Execute_Fetch sSQL
End If

' clear out potential existing references for this package
sSQL = "SET NOCOUNT ON DELETE FROM dbo.PackageStepXref WHERE
DTSPackageName='" & oPkg.Name & "' SELECT @@ROWCOUNT AS deletedCount"
nStepsDeleted = TSQL_Execute_Fetch(sSQL)

Dim oStep
nStepsInserted = 0
For Each oStep In oPkg.Steps
If oStep.Description <> "VB_Extract_Step_XRef" Then
sSQL = "INSERT INTO dbo.PackageStepXref (DTSPackageName,
DTSStepName, DTSStepDescription) VALUES ('" & oPkg.Name & "','" &
oStep.Name & "', '" & oStep.Description & "')"
TSQL_Execute_Fetch sSQL
nStepsInserted = nStepsInserted + 1
End If
Next
MsgBox "Total Steps Deleted: " & nStepsDeleted & vbNewLine & _
"Total Steps Inserted: " & nStepsInserted, , "Results of
VB_Extract_Step_XRef on " & oPkg.Name

Main = DTSTaskExecResult_Success
End Function

'************************************************* *********************
Function TSQL_Execute_Fetch(TSQL_Command)
Dim cn, rst
Dim oPkg
Set oPkg = DTSGLobalVariables.Parent
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=" & CONNECT_CAT &
";Application=TSQL_Execute;Data Source=" & CONNECT_DB
Set rst = CreateObject("ADODB.Recordset")

rst.Open TSQL_Command, cn
TSQL_Execute_Fetch = ""
On Error Resume Next
TSQL_Execute_Fetch = rst.Fields(0)
rst.Close
On Error Goto 0
Set rst = Nothing

'clean up
cn.Close
Set cn = Nothing
End Function