Tuesday, September 22, 2009

Figure out whether SSIS package was executed from JOB or not

  1. Write one SP, like this -
    ALTER PROCEDURE [dbo].[RunDTSPackages]
    @JobID VARCHAR(255)
    AS
    BEGIN
    SET ROWCOUNT 0
    DECLARE @DtexecQuery VARCHAR(8000)
    DECLARE @PackageName VARCHAR(200)
    DECLARE @DatabaseServer VARCHAR(100)
    DECLARE @ID INT
    DECLARE @JobSYSID VARCHAR(255)
    DECLARE @ReturnCode INT
    ----my package name to BLANK initially
    SET @PackageName = ''
    ----my server name
    SET @DatabaseServer = 'F1LT230'
    --Passing the SysJobID
    SET @JobSYSID ='/set \package.variables[SysJobID].Value;"\"'+@JobId+'\""'
    DECLARE @tblSSISMaster TABLE(
    ID INT PRIMARY KEY,
    Name VARCHAR(255),
    PackageName VARCHAR(255)
    )
    INSERT @tblSSISMaster SELECT
    ID,
    Name,
    PackageName
    FROM SSISPackageToRun
    WHERE IsMaster = 1 ORDER BY ID DESC
    SET ROWCOUNT 1
    SELECT @ID = ID FROM @tblSSISMaster
    WHILE @@ROWCOUNT <> 0
    BEGIN
    SELECT @PackageName = LEFT (PackageName, LEN(PackageName)-5) FROM @tblSSISMaster WHERE ID = @ID
    SET @DtexecQuery = 'dtexec /sq ' + @PackageName + ' /ser ' + @DatabaseServer + ' '
    SET @DtexecQuery = @DtexecQuery + @JobSYSID
    --Print @DtexecQuery
    EXEC @ReturnCode = xp_cmdshell @DtexecQuery
    DELETE FROM @tblSSISMaster WHERE ID = @ID
    SELECT @ID = ID FROM @tblSSISMaster
    END
    SET ROWCOUNT 0
    END
  2. Create a job and call above SP as under. Also ensure that you are logging the GUID generated from the job. Also, if same package is being executed from other way - using WEB App or directly, pass a hard coded GUID.
    DECLARE @SQL NVARCHAR(72), @jobID UNIQUEIDENTIFIER, @jobName SYSNAME
    SET @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)'
    EXEC sp_executesql @SQL,
    N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT
    EXEC RunDTSPackages @jobID
  3. Query the log table for the above two unique GUIDs and get the reports generated as in to know whether the package was executed from the Job or Web.
  4. Your log table should have following columns -
    ExecutionLogId]
    ,[ParentExecutionLogId]
    ,[Description]
    ,[PackageName]
    ,[PackageGuid]
    ,[MachineName]
    ,[ExecutionGuid]
    ,[LogicalDate]
    ,[Operator]
    ,[StartTime]
    ,[EndTime]
    ,[Status]
    ,[FailureTask]
    ,[tblsysJobID]

No comments:

Post a Comment