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