October 26, 2005 at 2:34 am
Hi,
I have diferent jobs scheduled in the system but I can´t find which Package is launched by the job. I only have this information: (double click over the job, steb tab, modify button for any job step, in the command text window)
DTSRun /~Z0x9D852D31537078274085C85BE05756CCE0CA78671EC12A4BDFFEC4E5E6017E4841EE5F41C492CCAA7F5746CA894011BB376479B6E679EC3C6045C328D1EF1CDA7CF28B6EEFE9DFE9237DF5662AE09BD6215C35AA4121BD2DE4433C7BABEE42EC87E70F47EA7C01FB44CB28
I would like to know the Package name related to.
Any help would be very appreciated.
Regards
October 27, 2005 at 6:19 am
Please, any hint?
Thanks
October 27, 2005 at 10:30 am
Right click on the job and do a Generate SQL Script. CLick the Preview button and look at the line for:
--Add the job
and take a look at the variable @description. It should state the package name that the job is executing, like this:
@description = N'Execute package: XXX'
October 27, 2005 at 11:55 am
That will work if whomever created the job put in a description. A job can be created without one.
There's no direct way to decrypt the DTSRUN parameters, but try this:
1.Run the package, either by starting the job or copying the DTSRUN line to a command prompt.
2.Then run this in QA:
select top 10 *
from msdb.dbo.sysdtspackagelog
order by endtime desc
You'll see the most recently run packages.
I've also read about a decrpytion routine called DecDTSRun but I haven't tried it. It can be downloaded here: http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26
Greg
Greg
October 28, 2005 at 10:07 am
For this very reason and a few others about examing exceptions in a package immediatly after it has run. We have wrote a sproc which using ole methods to load and execute a package.
CREATE PROCEDURE dbo.uspRunPackage
@sDTSPackageName sysname,
@sErrorSteps varchar(255) = NULL, -- A csv string of step names which we accept have errors and do not report
@sServername sysname = @@Servername,
@iConnectionFlag int = 256, -- 256 for trusted connection
@sUsername sysname = NULL,
@sPassword sysname = NULL,
@sEmailFrom varchar(255) = NULL, -- if this is null then no email reporting of errors is done
@sEmailTo varchar(255) = NULL,
@sLogServer sysname = NULL,
@ExecutionContext int = 4, -- 1 = in process, 4 = out process, 5 = in and out process
@ArgList varchar(4000) = NULL -- Arguments supplied as a csv seperated listed of attribute value pairs
--of the form <GlobalVar1>=<Value1>,<GlobalVar2>=<Value2>,
--
-- The purpose of this proc is to run a DTS job and mail any errors
-- The proc allows you to specify any steps for which errors should eb ignored
-- i.e. if they are already being handled elsewhere
--
AS
DECLARE @KnownErrorStep TABLE(StepName varchar(255))
DECLARE @hResult int,
@hPkg int,
@sSrc varchar(255),
@sDesc varchar(255),
@sStatusInfo varchar(8000),
@iStartPos int,
@iEndPos int,
@iLength int,
@hSteps int,
@hStp int,
@lStepCount int,
@idxStep int,
@sProperty varchar(30),
@sStepName varchar(40),
@sTaskName varchar(40),
@lExecResult int,
@lExecStatus int,
@lDisableStep int,
@bError bit,
@sEmailSubject varchar(255),
@sPkgID varchar(100),
@gLineageFullID uniqueidentifier,
@GlobalVarName varchar(255),
@GlobalVarValue varchar(1000),
@PropertyName varchar(255)
SET NOCOUNT ON
--Initialise
SET @sStatusInfo = ''
SET @bError = 0
--Create package object
EXEC @hResult = sp_OACreate 'DTS.Package2', @hPkg OUT, @ExecutionContext
IF @hResult <> 0
BEGIN
--failed to create package object
EXEC sp_OAGetErrorInfo NULL, @sSrc OUT, @sDesc OUT
SET @sStatusInfo = 'Failed to create DTS.Package2 Object.' + CHAR(13)+ CHAR(10) +
'Source: ' + @sSrc + CHAR(13)+ CHAR(10) +
'Description: ' + @sDesc + CHAR(13)+ CHAR(10)
SET @bError = 1
GOTO REPORT_STATUS
END
--Load package
EXEC @hResult = sp_OAMethod @hPkg , 'LoadFromSQLServer' , NULL ,
@ServerName = @sServerName,
@ServerUserName = @sUserName,
@ServerPassword = @sPassword,
@Flags = @iConnectionFlag,
@PackageName = @sDTSPackageName
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo NULL, @sSrc OUT, @sDesc OUT
SET @sStatusInfo = 'Failed to load DTS Package: ' + @sDTSPackageName + CHAR(13)+ CHAR(10) +
'Source: ' + @sSrc + CHAR(13)+ CHAR(10) +
'Description: ' + @sDesc + CHAR(13)+ CHAR(10)
SET @bError = 1
GOTO REPORT_STATUS
END
--Set log server if neccesary
IF @sLogServer IS NOT NULL
BEGIN
EXEC @hResult = sp_OASetProperty @hPkg, 'LogServerName', @sLogServer
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo NULL, @sSrc OUT, @sDesc OUT
SET @sStatusInfo = 'Failed to set Log Server for DTS Package: ' + @sDTSPackageName + CHAR(13)+ CHAR(10) +
'Source: ' + @sSrc + CHAR(13)+ CHAR(10) +
'Description: ' + @sDesc + CHAR(13)+ CHAR(10)
SET @bError = 1
GOTO REPORT_STATUS
END
END
--If there are global variables to set then do this now
IF @ArgList IS NOT NULL
BEGIN
--Iterate through each attribute value pair and set the appropriate global variable
WHILE LEN(@ArgList) > 0
BEGIN
--so first get position of =
SET @iStartPos = 1
SET @iEndPos = CHARINDEX('=', @ArgList, @iStartPos)
--next get global var name
SET @GlobalVarName = SUBSTRING(@ArgList, @iStartPos, @iEndPos - @iStartPos)
--next get global var value
SET @iStartPos = @iEndPos + 1
SET @iEndPos = CHARINDEX(',', @ArgList, @iEndPos)
IF @iEndPos = 0
SET @iEndPos = LEN(@ArgList) + 1
SET @GlobalVarValue = SUBSTRING(@ArgList, @iStartPos, @iEndPos - @iStartPos)
--take off args already processed
SET @ArgList = SUBSTRING(@ArgList, @iEndPos + 1, LEN(@ArgList) - @iEndPos + 1)
--Set the global var value
SET @PropertyName = 'GlobalVariables("' + @GlobalVarName + '").Value'
EXEC @hResult = sp_OASetProperty @hPkg, @PropertyName, @GlobalVarValue
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo NULL, @sSrc OUT, @sDesc OUT
SET @sStatusInfo = 'Failed to set global variable: ' + @GlobalVarName + ' in package ' + @sDTSPackageName + CHAR(13)+ CHAR(10) +
'Source: ' + @sSrc + CHAR(13)+ CHAR(10) +
'Description: ' + @sDesc + CHAR(13)+ CHAR(10)
SET @bError = 1
GOTO REPORT_STATUS
END
END
END
--Execute the package
EXEC @hResult = sp_OAMethod @hPkg, 'Execute', NULL
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo NULL, @sSrc OUT, @sDesc OUT
SET @sStatusInfo = 'Failed to execute DTS Package: ' + @sDTSPackageName + CHAR(13)+ CHAR(10) +
'Source: ' + @sSrc + CHAR(13)+ CHAR(10) +
'Description: ' + @sDesc + CHAR(13)+ CHAR(10)
SET @bError = 1
GOTO REPORT_STATUS
END
--Get package GUID
EXEC @hResult = sp_OAGetProperty @hPkg, 'PackageID', @sPkgID OUT
--Get Lineage ID
SET @gLineageFullID = (SELECT TOP 1 LineageFull
FROM msdb..sysdtspackagelog (nolock)
WHERE id = @sPkgID
ORDER BY logdate DESC)
--
-- If we get this far then the package managed to execute
-- so now need to find out if it was successful or not
--
-- First build up a table of setps which have known and acceptable errors
-- i.e. ones being reported in other ways
--
IF @sErrorSteps IS NOT NULL
BEGIN
--populate table from csv string
SET @iStartPos = 1
SET @iEndPos = 1
WHILE @iEndPos <> 0
BEGIN
--Get end position
SET @iEndPos = CHARINDEX(',', @sErrorSteps, @iStartPos)
IF @iEndPos > 0
BEGIN
SET @iLength = @iEndPos - @iStartPos
END
ELSE
SET @iLength = LEN(@sErrorSteps) - @iStartPos + 1
INSERT
INTO @KnownErrorStep
(StepName)
SELECT SUBSTRING(@sErrorSteps, @iStartPos, @iLength)
SET @iStartPos = @iEndPos + 1
END
SELECT * from @KnownErrorStep
END
--Get handle to package collection
EXEC @hResult = sp_OAGetProperty @hPkg, 'Steps', @hSteps OUT
EXEC @hResult = sp_OAGetProperty @hSteps, 'Count', @lStepCount OUT
SET @idxStep = 0
--Check each of the steps for execution information
WHILE @idxStep < @lStepCount
BEGIN
--Get reference to step at index idxstep
SET @idxStep = @idxStep + 1
SET @sProperty = 'Steps(' + Convert(varchar(10), @idxStep) + ')'
EXEC @hResult = sp_OAGetProperty @hPkg, @sProperty, @hStp OUT
--Step name
EXEC @hResult = sp_OAGetProperty @hStp, 'Name', @sStepName OUT
--Step task name
EXEC @hResult = sp_OAGetProperty @hStp, 'TaskName', @sTaskName OUT
--execution Status
EXEC @hResult = sp_OAGetProperty @hStp, 'ExecutionStatus', @lExecStatus OUT
--IsStep Disabled
EXEC @hResult = sp_OAGetProperty @hStp, 'DisableStep', @lDisableStep OUT
--Execution Result
EXEC @hResult = sp_OAGetProperty @hStp, 'ExecutionResult', @lExecResult OUT
--if step was executed then check if it failed or succeeded
IF @lExecStatus = 4 --Step completed
BEGIN
--if it failed = 1, (success = 0)
IF @lExecResult = 1
BEGIN
--Check if it is in list of steps that we do not want to
--report on
IF @sStepName NOT IN (SELECT StepName FROM @KnownErrorStep)
BEGIN
--Start to construct error string
IF @bError = 0
BEGIN
SET @sStatusInfo = 'Errors occurred on the following steps:' +
CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
SET @bError = 1
END
SET @sStatusInfo = (SELECT @sStatusInfo + @sStepName + CHAR(13) + CHAR(10) +
REPLICATE('-', LEN(@sStepName) + 2) +
CHAR(13) + CHAR(10) +
'Error Code: ' + CAST(ErrorCode as varchar(50)) +
CHAR(13) + CHAR(10) +
'Error Description: ' + ErrorDescription + CHAR(13) + CHAR(10) +
+ CHAR(13) + CHAR(10)
FROM msdb..sysDtsStepLog (nolock)
WHERE LineageFull = @gLineageFullID
AND Stepname = @sStepName)
END
END
END
END -- WHILE
REPORT_STATUS:
--Destroy the Ole Object Although it should be destroyed automatically at the
--end of the batch
EXEC @hResult = sp_OADestroy @hPkg
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo NULL, @sSrc OUT, @sDesc OUT
SET @sStatusInfo = @sStatusInfo + CHAR(13) + CHAR(10) +
'Failed to destroy OLE Object for DTS Package: ' + @sDTSPackageName + CHAR(13)+ CHAR(10) +
'Source: ' + @sSrc + CHAR(13)+ CHAR(10) +
'Description: ' + @sDesc + CHAR(13)+ CHAR(10)
SET @bError = 1
END
--if a we are to email error as well then do it now
IF @bError = 1
SET @sEmailSubject = 'DTS Package ''' + @sDTSPackageName + ''' on ' + @sServername + ' FAILED! @ ' +
CAST(GetDate() as varchar(30))
IF (@sEmailFrom IS NOT NULL) AND (@sEmailTo IS NOT NULL) AND (@bError = 1)
BEGIN
EXEC Master.dbo.sp_dba_sendmail @sEmailFrom, @sEmailTo, @sEmailSubject, @sStatusInfo
END
SET NOCOUNT OFF
--message success or failure and raiseerror if necceserry
If @bError = 1
BEGIN
PRINT @sEmailSubject + CHAR(13) + CHAR(10) + @sStatusInfo
RAISERROR (@sEmailSubject, 16, 1)
END
ELSE
PRINT 'Execution of Package ''' + @sDTSPackageName + ''' on ' + @sServername + ' Completed Successfully @ ' +
CAST(GetDate() as varchar(30))
GO
Thanks Jeet
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply