Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Packages related with its jobs...


Packages related with its jobs...

Author
Message
Oscar Hernández
Oscar Hernández
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1

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


Oscar Hernández
Oscar Hernández
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1

Please, any hint?

Thanks


rocky
rocky
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 246
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'
Greg Charles
Greg Charles
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4089 Visits: 5823

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
Jeet
Jeet
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 11

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search