September 15, 2006 at 9:23 am
When I execute a child package from a master DTS I don't get the task status list display as when running the child package alone. Does anyone know of a way to display the task status for a child package so you can see the counts and execute times?
Thanks so much
September 15, 2006 at 10:05 am
You need to enable logging of the package outer and inner to SQL server, best on the server it is running\saved to. You right click in the empty space in the design pane and choose logging to achive this. during the dts run, or after you can run the below script which should give you what you need. Got both scrips from here in the past:
USE
msdb
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
GO
create
function [dbo].[SFormat7398zzzz](@SecTime as int)
RETURNS
Varchar(10)
As
Begin
Declare
@Hour as Varchar(4)
Declare
@min-2 as Varchar(2)
Declare
@sec as varchar(2)
Declare
@RetTime as varchar(10)
Select
@Hour=Round(@SecTime/3600,0,1)
If
Len(@Hour)=1
BEGIN
select @Hour = '0'+ @Hour
End
Select
@min-2=Round((@SecTime % 3600)/60 ,0,1)
If
Len(@min-2)=1
BEGIN
End
Select
@sec = (@SecTime % 3600)%60
If
Len(@sec)=1
BEGIN
End
set
@RetTime=@Hour + ':' + @min-2 + ':' + @sec
Return
(@RetTime)
end
go
USE
msdb
go
DECLARE
@package_name sysname
SET
@package_name = 'PasteYourPackageNameHere'
SELECT
dsl
.stepname AS [Step Name]
, CASE dsl.stepexecstatus
WHEN 1 THEN 'DTSStepExecStat_Waiting'
WHEN 2 THEN 'DTSStepExecStat_InProgress'
WHEN 3 THEN 'DTSStepExecStat_Inactive'
WHEN 4 THEN 'DTSStepExecStat_Completed'
END AS [Step Execute Status]
, CASE dsl.stepexecresult
WHEN 0 THEN 'DTSStepExecResult_Success'
WHEN 1 THEN 'DTSStepExecResult_Failure'
END AS [Step Execute Result]
, dsl.starttime AS [Start Time]
, dsl.endtime AS [End Time]
, [dbo].[SFormat7398zzzz](dsl.elapsedtime) AS [Elapsed Time H:M:S]
, dsl.elapsedtime AS [Elapsed Time Seconds]
, dsl.progresscount AS [Row Count]
, dsl.errorcode AS [Error Code]
, dsl.errordescription AS [Error Description]
FROM
dbo
.sysdtspackagelog dpl (nolock)
INNER JOIN dbo.sysdtssteplog dsl (nolock)
ON dpl.lineagefull = dsl.lineagefull
WHERE
dpl
.logdate = -- gets last run details, even if the package is still running
(
SELECT
MAX(dpl1.logdate)
FROM
dbo
.sysdtspackagelog dpl1 (nolock)
WHERE
dpl1
.name = dpl.name)
--where clause can be change to to look for particular date period
--WHERE dpl.logdate between '2006-07-06 10:13:37.780' and '2006-07-06 19:13:37.780'
AND dpl.name = @package_name
ORDER
BY
dpl
.starttime
DROP
FUNCTION [dbo].[SFormat7398zzzz]
GO
September 15, 2006 at 11:50 am
So... if I don't have permissions to run anything on msdb on the Server running the DTS then this won't work. Any other ideas.
Thanks,
September 15, 2006 at 2:18 pm
Permission issues where not mentioned in you previous post, feel free to figure this out on your own.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply