Packages related with its jobs...

  • 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

  • Please, any hint?

    Thanks

  • 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'

  • 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

  • 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