January 30, 2012 at 2:06 pm
Hi All, need help.
Need to create Monitor for SQL Server SSIS Packages, which should satisfy following conditions.
1)Each job should have the owner as SA
2)Each job step calling an SSIS package must pass a parameter of ParentName.
3)The value for the ParentName must be the same as the job name.
urgent help needed
Thanks in advance.
January 31, 2012 at 3:14 am
What exactly do you want to monitor? From your request it looks like you are just looking for the jobhistory and not really for SSIS specific logging.
Something similar to this should work:
DECLARE @jobname sysname,@startd datetime, @end datetime, @step int
SELECT @jobname = 'YourJob', @startd = '20120130', @end = '20120131',@step = 1
SELECT j.name AS jobname, h.step_name,
CASE run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In progress'
ELSE 'Unknown'
END AS runstatus,
CASE run_date
WHEN 0 THEN NULL
ELSE CONVERT(DATETIME, STUFF(STUFF(CAST(run_date AS NCHAR(8)), 7, 0, '-'), 5, 0, '-') + N' ' + STUFF(STUFF(SUBSTRING(CAST(1000000 + run_time AS NCHAR(7)), 2, 6), 5, 0, ':'), 3, 0, ':'), 120)
END AS rundate,
CASE LEN(run_duration)
WHEN 1 THEN CAST('00:00:0' + CAST(run_duration AS CHAR) AS CHAR(8))
WHEN 2 THEN CAST('00:00:' + CAST(run_duration AS CHAR) AS CHAR(8))
WHEN 3 THEN CAST('00:0' + LEFT(RIGHT(run_duration, 3), 1) + ':' + RIGHT(run_duration, 2) AS CHAR(8))
WHEN 4 THEN CAST('00:' + LEFT(RIGHT(run_duration, 4), 2) + ':' + RIGHT(run_duration, 2) AS CHAR(8))
WHEN 5 THEN CAST('0' + LEFT(RIGHT(run_duration, 5), 1) + ':' + LEFT(RIGHT(run_duration, 4), 2) + ':' + RIGHT(run_duration, 2) AS CHAR(8))
WHEN 6 THEN CAST(LEFT(RIGHT(run_duration, 6), 2) + ':' + LEFT(RIGHT(run_duration, 4), 2) + ':' + RIGHT(run_duration, 2) AS CHAR(8))
END AS duration
FROM msdb.dbo.sysjobhistory AS h INNER JOIN
msdb.dbo.sysjobs AS j ON h.job_id = j.job_id AND j.category_id = 103 AND h.step_id = @step INNER JOIN
(SELECT job_id, AVG(CASE WHEN LEN(run_duration) < 3 THEN run_duration WHEN LEN(run_duration)
= 3 THEN (run_duration / 100 * 60 + run_duration % 100) WHEN LEN(run_duration) = 4 THEN ((run_duration / 100 * 60)
+ run_duration % 100) WHEN LEN(run_duration) > 4 THEN (run_duration / 10000 * 3600) + ((run_duration - (run_duration / 10000 * 10000))
/ 100 * 60) + run_duration % 100 END) AS duration
FROM msdb.dbo.sysjobhistory
WHERE (CASE run_date WHEN 0 THEN NULL ELSE CONVERT(DATETIME, STUFF(STUFF(CAST(run_date AS NCHAR(8)), 7, 0, '-'), 5, 0, '-')
+ N' ' + STUFF(STUFF(SUBSTRING(CAST(1000000 + run_time AS NCHAR(7)), 2, 6), 5, 0, ':'), 3, 0, ':'), 120) END BETWEEN @startd AND
@end) AND (step_id = @step)
GROUP BY job_id) AS q1
ON j.job_id = q1.job_id AND h.step_id = @step
WHERE (CASE run_date WHEN 0 THEN NULL ELSE CONVERT(DATETIME, STUFF(STUFF(CAST(run_date AS NCHAR(8)), 7, 0, '-'), 5, 0, '-')
+ N' ' + STUFF(STUFF(SUBSTRING(CAST(1000000 + run_time AS NCHAR(7)), 2, 6), 5, 0, ':'), 3, 0, ':'), 120) END BETWEEN @startd AND DATEADD(hh, 23,
@end)) AND (j.name = @jobname)
In case you need more SSIS specific info use the logging options in the packages and the sysssislog table in msdb
[font="Verdana"]Markus Bohse[/font]
January 31, 2012 at 4:41 pm
Thanks Mark,
When I Run the script, In result pannel, nothing is coming up.
We have few SSIS packages on SA and orther Logins, I need to know which Jobs are runing under SA ownership.
Thanks in advance.
February 1, 2012 at 12:40 am
To find jobs which have a different owner than sa use this:
select * from msdb..sysjobs
where owner_sid <> 0x01
The reason you don't get any results from my first script is probably that you need to change some of the parameters. I also noticed that there is still a condition included that the jobcategaory must be 103, which is something specific to my enviroment, so just remove this part "AND j.category_id = 103" and it should work.
Anyhow I'm still not sure what exactly you try to monitor. Is it just the jobowner and status, or are you looking for more?
[font="Verdana"]Markus Bohse[/font]
February 2, 2012 at 3:47 pm
I have tried everying thing, But it is not wokring.
Searching the following servers for: Jobs that run SSIS packages
Each job should have the owner as SA
Each job step calling an SSIS package must pass a parameter of ParentName
The value for the ParentName must be the same as the job name.
which should the Job name, Job Owner of SA, Each job step calling an SSIS package must pass a parameter of ParentName and The value for the ParentName must be the same as the job name.
Can anyone please help me.
thanks in advance.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply