- 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 - 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 - 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.
- 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