Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Packages related with its jobs... Expand / Collapse
Author
Message
Posted Wednesday, October 26, 2005 2:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 12, 2007 2:58 AM
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

Post #232301
Posted Thursday, October 27, 2005 6:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 12, 2007 2:58 AM
Points: 8, Visits: 1

Please, any hint?

Thanks

Post #232875
Posted Thursday, October 27, 2005 10:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 19, 2014 9:38 AM
Points: 92, Visits: 240
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'
Post #233029
Posted Thursday, October 27, 2005 11:55 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, August 7, 2014 3:54 PM
Points: 4,065, Visits: 5,296

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
Post #233068
Posted Friday, October 28, 2005 10:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 15, 2009 2:24 AM
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
Post #233401
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse