SQL Script to pull DTS/JOB Start/Stop/Duration

Question

Post reply

SQL Script to pull DTS/JOB Start/Stop/Duration

HelpMeInOhio

Hall of Fame

Points: 3945
More actions
June 14, 2004 at 11:18 am

#360146

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!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

hirenk SSCrazy Points: 2000 More actions · Answer 1

hirenk

SSCrazy

Points: 2000

June 14, 2004 at 1:53 pm

#510339

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.

bobby henningsen Mr or Mrs. 500 Points: 521 More actions · Answer 2

bobby henningsen

Mr or Mrs. 500

Points: 521

June 15, 2004 at 3:44 am

#510407

You can write a script querying sysjobhistory and sysdtspackagelog. It contains the information desired.

Tom Hamilton-126456 Mr or Mrs. 500 Points: 527 More actions · Answer 3

Tom Hamilton-126456

Mr or Mrs. 500

Points: 527

June 15, 2004 at 8:44 am

#510472

DTS has such logging built into it. Just enable it through DTS Package Properties

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 4

HelpMeInOhio

Hall of Fame

Points: 3945

June 15, 2004 at 8:48 am

#510474

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?

Tom Hamilton-126456 Mr or Mrs. 500 Points: 527 More actions · Answer 5

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

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 6

HelpMeInOhio

Hall of Fame

Points: 3945

June 15, 2004 at 9:56 am

#510492

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 : <.

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 7

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

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

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

philcart SSC-Forever Points: 47794 More actions · Answer 8

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

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 9

HelpMeInOhio

Hall of Fame

Points: 3945

June 16, 2004 at 6:56 am

#510625

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

philcart SSC-Forever Points: 47794 More actions · Answer 10

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