Post reply

[SQL SERVER AGENT] related query

  • 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 )

  • June 26, 2017 at 9:23 am

    #1948176

    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!

  • June 26, 2017 at 9:30 am

    #1948177

    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

  • June 26, 2017 at 9:39 am

    #1948178

    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

  • June 26, 2017 at 9:54 am

    #1948181

    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

  • June 26, 2017 at 10:12 am

    #1948185

    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

  • June 26, 2017 at 10:35 am

    #1948193

    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.

  • June 27, 2017 at 2:07 am

    #1948309

    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

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy