June 23, 2016 at 7:34 am
Hello Everyone,
I have over 50 SQL agent jobs with SSIS packages and over 120 Databases on my SQL server. I would like to start removing unused databases, but before I can start removing database, I need to find out if there are any SQL Agent jobs that run on that database.
Is there a way to see what database the SQL Agent job touches without opening them one by one?
June 23, 2016 at 7:43 am
SQL Agent Jobs information is stored in msdb databases.
But In latest version of SQL Server say SQL 2012 on wards, the SQL Agent Jobs info is directly saved into SSISCatalogue databases.
So depending on your SQL Server version, search eithr msdb or SSISCatalogue db for the required data.
June 23, 2016 at 7:50 am
durga.palepu (6/23/2016)
SQL Agent Jobs information is stored in msdb databases.But In latest version of SQL Server say SQL 2012 on wards, the SQL Agent Jobs info is directly saved into SSISCatalogue databases.
So depending on your SQL Server version, search eithr msdb or SSISCatalogue db for the required data.
SQL Agent Jobs are not saved into the SSIS Catalog. The SSIS catalog contains details of packages which have been deployed using the Project deployment method.
The issue is unless all the properties in the SSIS package are parameterized then you won't know exactly what database it is connecting to without having access to the source.
June 23, 2016 at 7:55 am
Thanks for correcting me.
June 23, 2016 at 8:08 am
Hello,
No sure if this will help, but in SQL job. In the Configuration tab of the SSIS package step, there is a parameters tab that has a field called DBName.
Can I query this information?
June 23, 2016 at 8:20 am
AlianaRivera887 (6/23/2016)
Hello,No sure if this will help, but in SQL job. In the Configuration tab of the SSIS package step, there is a parameters tab that has a field called DBName.
Can I query this information?
An SSIS package is nothing but a giant XML file, so if you know the structure of the package, you can query it for the value in that field.
John
June 23, 2016 at 8:48 am
Does this return the value of that parameter? Remember though just cause it has a parameter called dbname it doesn't mean thats the only db it accesses.
SELECT object_name, parameter_name, design_default_value, default_value FROM SSISDB.internal.object_parameters
WHERE parameter_name = 'DBName'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply