SQL Script to pull DTS/JOB Start/Stop/Duration
-
Our major solution components are a DTS Mapping and a few Jobs. Every morning someone comes in and manually checks history and logs for Start, Finish and Duration Information for the DTS Mapping and the few jobs. Is there an SQL script I can write to query the applicable tables to pull this information?
Thanks!
-
I know that there are few scripts on this board that were pulling job history for all the jobs on one server. Search Scripts on this site and you are likely to find something.
-
You can write a script querying sysjobhistory and sysdtspackagelog. It contains the information desired.
-
DTS has such logging built into it. Just enable it through DTS Package Properties
-
I knew about this but he process ... when you have hundreds of DTS ... is very tedious. If the data were in flat files or the database (where I could do SQL LIKE queries) it would work better. Any ideas ... other than the tedious properties approach?
-
As you say - they are tough to read. I usually create a system activity table and use a stored procedure called from the DTS package to record the activity. Here's two pieces of code, first creates my tblSystemActivity, the second is the stored procedure I use to post activity :
----------------------------------------------------
CREATE TABLE [dbo].[tblSystemActivity] (
[SystemID] [bigint] IDENTITY (1, 1) NOT NULL ,
[ActionType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EntryDateTime] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_tblSystemActivity] ON [dbo].[tblSystemActivity]([SystemID] DESC ) ON [PRIMARY]
GO
CREATE INDEX [IX_tblSystemActivity_Description] ON [dbo].[tblSystemActivity]([Description]) ON [PRIMARY]
GO
----------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spPostToSysLog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spPostToSysLog]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spPostToSysLog
@sLogType varchar(20),
@sLogDesc varchar(255)
As
DECLARE @mvType varchar(20)
DECLARE @mvDesc varchar(255)
SET @mvType = @sLogType
SET @mvDesc = @sLogDesc
Insert tblSystemActivity
(ActionType, [Description], UserName)
values (@mvType, @mvDesc, Current_User)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spPostToSysLog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spPostToSysLog]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spPostToSysLog
@sLogType varchar(20),
@sLogDesc varchar(255)
As
DECLARE @mvType varchar(20)
DECLARE @mvDesc varchar(255)
SET @mvType = @sLogType
SET @mvDesc = @sLogDesc
Insert tblSystemActivity
(ActionType, [Description], UserName)
values (@mvType, @mvDesc, Current_User)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------
Call the sp like this from within the keypoints of your DTS package:
Exec spPostToSyslog 'Data Load', 'Begin CAI DELTA'
where 'Data Load' - is the this particular step
'Begin CAI DELTA' - is the sub package identifier.
Since the tblSystemActivity auto-stamps each entry then it's just a matter of using a little date math to extract the elapsed time.
hth
-
This looks nice but we load massive data warehouses here so I wouldn't want to add overhead. It is hard for me to believe that the metadata does not exist out there about DTS solutions : <.
-
What I like to do is have a DTS (a) that calls the DTS (b) that I want to log information about. The calling DTS (a) contains logging code as well as code to execute the desired DTS (b). Everything is contained in an ActiveX Script...
Function Main()
'Declare variables
Dim objPkg
Dim iCount
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' BEGIN LOG PROCESS . . .
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Declare variables
Dim my_LOG_pkg
Dim my_LOG_task
Dim my_Log_Msg
Dim my_Log_flag
THIS IS SIMPLY AN EXECUTE SQL TASK THAT APPENDS A RECORD TO A STATIC TABLE
'Initialize LOG package
Set my_LOG_pkg = CreateObject("DTS.Package")
my_LOG_pkg.LoadFromSQLServer "your_Server_Name", , , DTSSQLStgFlag_UseTrustedConnection,,,, _
"your_DTS_Package_Name_used_for_Logging"
Set my_LOG_task = my_LOG_pkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask
'-----------------------------------------------------------------------------------------------------------------------------------
' START EXECUTING DTS PACKAGE
'-----------------------------------------------------------------------------------------------------------------------------------
'Create and Execute the package
Set objPkg = CreateObject("DTS.Package")
objPkg.LoadFromSQLServer "your_Server_Name", , , DTSSQLStgFlag_UseTrustedConnection,,,, _
"your_DTS_Package_Name_to_be_Run"
objPkg.Execute
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' LOG PROCESS . . .
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Check For Errors
my_Log_flag = "Successful thus Continue DTS"
For iCount = 1 To objPkg.Steps.Count
If objPkg.Steps(iCount).ExecutionResult = 0 Then
my_Log_Msg = "Successful"
Else
my_Log_Msg = "Failed"
my_Log_flag = "Failed thus Stop DTS"
End If
my_LOG_task.SQLStatement = "exec usp_Save_Log_Results " & _
"'" & objPkg.Name & "', " & _
"'" & objPkg.Steps(iCount).Description & "', " & _
"'" & objPkg.Steps(iCount).Name & "', " & _
"'" & my_Log_Msg & "', " & _
"'" & objPkg.Steps(iCount).StartTime & "', " & _
"'" & objPkg.Steps(iCount).FinishTime & "'"
my_LOG_pkg.Execute
THIS APPENDS THE INFORMATION ON EACH STEP TO THE LOGGING TABLE
Next
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set objPkg = Nothing
If my_Log_flag = "Failed thus Stop DTS" Then
Set my_LOG_task = Nothing
Set my_LOG_pkg = Nothing
Main = DTSTaskExecResult_Failure
Exit Function
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END LOG PROCESS . . .
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Clean-up objects
Set my_LOG_task = Nothing
Set my_LOG_pkg = Nothing
Main = DTSTaskExecResult_Success
End Function
-
Here's what we have for our status check. This is used to check on 80+ packages, of which about a dozen run on an hourly basis, the rest run overnight.
It has two optional parameters, one is the start datetime of the step the other is the DTS step name. This way we can look at just the DataPump tasks
eg: EXEC dbo.usp_GetDTSPkgLog '16 Jun 2004 13:00', 'Datapump'
gives us all the Datapump steps since 1pm on June 16th.
CREATE PROCEDURE dbo.usp_GetDTSPkgLog @dteMinStart datetime = NULL , @vcrStep nvarchar(128) = NULL AS BEGIN SET NOCOUNT ON SET DATEFORMAT dmy DECLARE @vcrStepLike nvarchar(128) IF @dteMinStart IS NULL SET @dteMinStart = CONVERT(varchar(15), GetDate(), 106) SET @vcrStepLike = '%' + ISNULL(@vcrStep, '') + '%' SELECT Pkg.name , Stp.stepname , Stp.starttime , CAST(stp.elapsedtime as decimal(9,2)) as ElapsedTime , stp.progresscount , stp.errorcode , stp.errordescription FROM [msdb].[dbo].[sysdtssteplog] Stp INNER JOIN [msdb].[dbo].[sysdtspackagelog] Pkg ON stp.lineagefull = Pkg.lineagefull WHERE Stp.starttime > @dteMinStart And Stp.stepname like @vcrStepLike ORDER BY Stp.[starttime] DESC END
If setting the logging properties for the DTS packages is a problem, I can give you a short ActiveScript that will accomplish that task.
--------------------
Colt 45 - the original point and click interface
-
A script would be nice. If I run it wide open will get everything from the beginning of time?
By the way ... you are an angel.
Thanks
-
If you run the stored procedure without any parameters it will return everything since midnight. If you want to change that just alter the line after the "IF @dteMinStart IS NULL" statement.
Heres the ActiveScript to turn on package logging. Just create a DTS package with an ActiveScript task and copy this into it. Then you can right-clcik on the task to execute the step.
It is important to note that using this script will cause any Text annotations and layout to be lost. This is because they are not maintained as part of the DTS object model. If you can't live without the annotations and layout then don't use this script.
Option Explicit Function Main() Dim oSrvr ' SQL-DMO server object Dim oDb ' SQL-DMO database object Dim oQry ' SQL-DMO Query results object Dim sSQL ' SQL string to execute Dim iRow ' loop counter Dim iMaxRows ' number of rows in query results Dim oPkg ' DTS package object Dim sPkgName ' DTS Package name Dim sServerName ' server name Dim iSec ' Type of security Dim sUsername ' Username for SQL security Dim sPassword ' password for SQL security ' ********************************************** ' The following variables need to be set ' ********************************************** ' the server name to access sServerName = "(local)" ' set security mode to use, Windows (256) or SQL (1) iSec = 256 ' if using SQL Security, need to supply valid username and password sUsername = "" sPassword = "" ' build SQL string to retrieve list of all DTS packages ' add where clause to limit package selection sSQL = "SELECT DISTINCT name FROM msdb.dbo.sysdtspackages'" ' use SQL-DMO to connect to server and retrieve list of packages Set oSrvr = CreateObject("SQLDMO.SQLServer") oSrvr.LoginSecure = True oSrvr.Connect sServerName Set oDb = oSrvr.Databases("msdb") Set oQry = oDb.ExecuteWithResults(sSQL) iRow = 1 iMaxRows = oQry.Rows ' check if there are rows in the queryresults object If iMaxRows > 0 Then ' loop through query results using iRow as the loop counter While iRow <= iMaxRows ' get the package name from the query results sPkgName = oQry.GetColumnString(iRow, 1) Set oPkg = CreateObject("DTS.Package") ' load the package oPkg.LoadFromSQLServer sServerName, sUsername, sPassword, iSec, "", "", "", sPkgName ' change the logging properties oPkg.LogToSQLServer = 1 oPkg.LogServerName = sServerName oPkg.LogServerFlags = iSec oPkg.LogServerUserName = sUsername oPkg.LogServerPassword = sPassword ' save the package back to the server oPkg.SaveToSQLServer sServerName, sUsername, sPassword, iSec Set oPkg = Nothing iRow = iRow + 1 Wend Else MsgBox "No packages found on server " & sServerName End If Main = DTSTaskExecResult_Success End Function--------------------
Colt 45 - the original point and click interface
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply