February 24, 2012 at 9:19 am
Hi all,
I am tasked to find out all jobs calling SSIS packages. We have too many jobs on our server, and to check them manually would be too tedious. I want to write a query using some sys... tables in msdb. Is there a way to do it ?
Thanks
February 24, 2012 at 9:53 am
I would use something like this.
select j.name, js.step_name, js.command
from dbo.sysjobsteps js
inner join dbo.sysjobs j on js.job_id = j.job_id
where command like '%DTEXEC.exe%'
February 24, 2012 at 9:54 am
Here's a starter query.
SELECT DISTINCT j.name
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps js
ON j.job_id = js.job_id
WHERE js.subsystem = 'SSIS'
This relies on the job step having been defined as an SSIS task. If the job step is a different task type then it won't trap that the job may be SSIS related.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 24, 2012 at 9:56 am
Merging the two queries (since SSIS subtypes do not use dtexec)
SELECT DISTINCT j.name
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps js
ON j.job_id = js.job_id
WHERE js.subsystem = 'SSIS'
OR js.command LIKE '%DTEXEC.exe%'
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 24, 2012 at 9:59 am
SQLRNNR (2/24/2012)
Merging the two queries (since SSIS subtypes do not use dtexec)
SELECT DISTINCT j.name
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps js
ON j.job_id = js.job_id
WHERE js.subsystem = 'SSIS'
OR js.command LIKE '%DTEXEC.exe%'
You beat me to it since they could appear in either format
February 24, 2012 at 10:01 am
MysteryJimbo (2/24/2012)
SQLRNNR (2/24/2012)
Merging the two queries (since SSIS subtypes do not use dtexec)
SELECT DISTINCT j.name
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps js
ON j.job_id = js.job_id
WHERE js.subsystem = 'SSIS'
OR js.command LIKE '%DTEXEC.exe%'
You beat me to it since they could appear in either format
We still haven't accounted for if the package is executed via stored proc. 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 24, 2012 at 10:08 am
Thanks all. This works good.
And about packages which are executed via stored proc, I hope we do not ahave such, but just in case sys.sql_modules.
Thanks
February 24, 2012 at 10:23 am
Yes. Just search for the dtexec string in modules.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 24, 2012 at 10:24 am
SQLRNNR (2/24/2012)
MysteryJimbo (2/24/2012)
SQLRNNR (2/24/2012)
Merging the two queries (since SSIS subtypes do not use dtexec)
SELECT DISTINCT j.name
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps js
ON j.job_id = js.job_id
WHERE js.subsystem = 'SSIS'
OR js.command LIKE '%DTEXEC.exe%'
You beat me to it since they could appear in either format
We still haven't accounted for if the package is executed via stored proc. 😉
I wasnt asked to 🙂
Not directly any way.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply