[SQL SERVER AGENT] related query

Question

[SQL SERVER AGENT] related query

mw_sql_developer

SSCoach

Points: 19445
More actions
June 26, 2017 at 9:09 am

#402217

Take a look at he picture.
We have many jobs. How can I write a SELECT statement that would tell me which job uses a particular dtsx file as a part of the job.
I understand a job can have many steps.
But lets make this simple, if I the query can tell me JOB A uses the following dtsx files ( That is good enough for me )
Similarly if you could write a query that shows, This DTSX file uses this Stored procedure ( Now that it outside the topic here but if you could help me that would be great )

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

I don't have access to an appropriate server to test, but msdb.dbo.sysjobsteps should have a reference to the package used in the command column.

Could just query for command LIKE '%YourPackageName%' and join sysjobsteps to sysjobs to get the job name.

Untested, though, so don't quote me on this 🙂

Cheers!

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Jacob Wilkins - Monday, June 26, 2017 9:23 AM
I don't have access to an appropriate server to test, but msdb.dbo.sysjobsteps should have a reference to the package used in the command column.
Could just query for command LIKE '%YourPackageName%' and join sysjobsteps to sysjobs to get the job name.
Untested, though, so don't quote me on this 🙂
Cheers!

OK, let me progress and get back to you

Thom A SSC Guru Points: 99130 More actions · Answer 3

Something like:
SELECT * FROM dbo.sysjobsteps sjs WHERE sjs.subsystem = 'SSIS' AND sjs.command LIKE '/ISSERVER "\"\Agile\"%';
This would return all the job steps that reference a package in the Agile solution, provided they are deployed via the SSISDB.

You can't really do a lot to find out what SP's a package references. You could have a look at Brandie's article, however, this is for looking for a specific SP/objects in an SSIS solution, not finding what objects an SSIS solution references.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

Thom A - Monday, June 26, 2017 9:39 AM
Something like:
SELECT * FROM dbo.sysjobsteps sjs WHERE sjs.subsystem = 'SSIS' AND sjs.command LIKE '/ISSERVER "\"\Agile\"%';
This would return all the job steps that reference a package in the Agile solution, provided they are deployed via the SSISDB.
You can't really do a lot to find out what SP's a package references. You could have a look at Brandie's article, however, this is for looking for a specific SP/objects in an SSIS solution, not finding what objects an SSIS solution references.

I believe there may be some table in the DB where the content of a dtsx file is stored in a colum ? Right ? What can that table name be ? ( I mean the table Catalog under Reportserver stores the entire SSRS report as an xml stream ) . I am sure there is a table out there that stores dtsx files

Thom A SSC Guru Points: 99130 More actions · Answer 5

mw112009 - Monday, June 26, 2017 9:54 AM
Thom A - Monday, June 26, 2017 9:39 AM
Something like:
SELECT * FROM dbo.sysjobsteps sjs WHERE sjs.subsystem = 'SSIS' AND sjs.command LIKE '/ISSERVER "\"\Agile\"%';
This would return all the job steps that reference a package in the Agile solution, provided they are deployed via the SSISDB.
You can't really do a lot to find out what SP's a package references. You could have a look at Brandie's article, however, this is for looking for a specific SP/objects in an SSIS solution, not finding what objects an SSIS solution references.
I believe there may be some table in the DB where the content of a dtsx file is stored in a colum ? Right ? What can that table name be ? ( I mean the table Catalog under Reportserver stores the entire SSRS report as an xml stream ) . I am sure there is a table out there that stores dtsx files

Not in SSIS, no, the packages are stored as a varbinary. You can't simply "query" them.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

mw_sql_developer SSCoach Points: 19445 More actions · Answer 6

Thom A - Monday, June 26, 2017 10:12 AM
mw112009 - Monday, June 26, 2017 9:54 AM
Thom A - Monday, June 26, 2017 9:39 AM
Something like:
SELECT * FROM dbo.sysjobsteps sjs WHERE sjs.subsystem = 'SSIS' AND sjs.command LIKE '/ISSERVER "\"\Agile\"%';
This would return all the job steps that reference a package in the Agile solution, provided they are deployed via the SSISDB.
You can't really do a lot to find out what SP's a package references. You could have a look at Brandie's article, however, this is for looking for a specific SP/objects in an SSIS solution, not finding what objects an SSIS solution references.
I believe there may be some table in the DB where the content of a dtsx file is stored in a colum ? Right ? What can that table name be ? ( I mean the table Catalog under Reportserver stores the entire SSRS report as an xml stream ) . I am sure there is a table out there that stores dtsx files
Not in SSIS, no, the packages are stored as a varbinary. You can't simply "query" them.

I know how to convert them from varbinary. Just tell me the name of the table.

Thom A SSC Guru Points: 99130 More actions · Answer 7

mw112009 - Monday, June 26, 2017 10:35 AM
Thom A - Monday, June 26, 2017 10:12 AM
mw112009 - Monday, June 26, 2017 9:54 AM
Thom A - Monday, June 26, 2017 9:39 AM
Something like:
SELECT * FROM dbo.sysjobsteps sjs WHERE sjs.subsystem = 'SSIS' AND sjs.command LIKE '/ISSERVER "\"\Agile\"%';
This would return all the job steps that reference a package in the Agile solution, provided they are deployed via the SSISDB.
You can't really do a lot to find out what SP's a package references. You could have a look at Brandie's article, however, this is for looking for a specific SP/objects in an SSIS solution, not finding what objects an SSIS solution references.
I believe there may be some table in the DB where the content of a dtsx file is stored in a colum ? Right ? What can that table name be ? ( I mean the table Catalog under Reportserver stores the entire SSRS report as an xml stream ) . I am sure there is a table out there that stores dtsx files
Not in SSIS, no, the packages are stored as a varbinary. You can't simply "query" them.
I know how to convert them from varbinary. Just tell me the name of the table.

"Please"? There's no need to demand.

I didn't know the table, however, I took the time to look this morning (there's only 30 tables in the SSISDB, so it took me a whole 5 minutes). It's in internal.object_versions called object_data.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk