Automated Inventory Collection of Scheduled SQL Server Integration Services Packages

By:   |   Comments (2)   |   Related: > Integration Services Administration


Problem

At my company, we use SQL Server Integration Services (SSIS) packages extensively (several hundred and growing), and these packages are generally stored in the SSIS Catalog on a few dedicated SSIS servers and there are also some legacy systems that use SSIS packages stored in shared folders or MSDB.

These SSIS packages are all used by SQL Server Agent Jobs, to better understand the relationships between these jobs and the packages, I want to create an inventory table which can tell me what job on which server is using which SSIS packages, how can I do this?

This is a common scenario to DBAs especially in environments where multiple SSIS packages are used. After several years, due to business requirement changes, some SSIS packages may become "ghost" packages, i.e. they are no longer used, yet, nobody dares to remove them or even cares to do the clean up, because we cannot validate the usage.

Solution

In this tip, we will explore how to extract the needed information from SQL Server Agent Jobs. This tip is mainly addressing SQL Server 2012 and above versions and focuses on three types of SSIS storages: SSIS catalog, msdb, and file system.

I will omit the detailed steps about creating SSIS packages, which you can find reference links in the [Next Steps] section and assume you already have SQL Server Agent Jobs with steps running SSIS packages.

Collecting the SSIS package data

We can easily find such jobs with the following T-SQL:

-- find job steps that execute SSIS packages
use msdb
select [job]=j.name, [step]=s.step_name, s.command 
from dbo.sysjobsteps s
inner join dbo.sysjobs j
on s.job_id = j.job_id
and s.subsystem ='SSIS' 
go

A sample result will be like the following:

ssis package location

In [job 1], the SSIS package is stored in a shared folder, i.e. \\ssis_svr_1\ssis$\, for [job 2] the package is stored in the SSIS catalog, while for [job 3], the SSIS package is stored in msdb on SQL Server instance [DevSvr\sql2017]

If we look at the [command] column, we can see there is a pattern of how a command is constructed by the SQL Server Agent job.  It is like the following:

/<storage type> "<ssis full location>" [/Server <server name>] <other parameters>

Here:

for <storage type>

  • FILE - meaning the SSIS package is stored in a file system storage
  • ISSERVER - meaning the SSIS package is stored in an SSIS catalog (applicable to SQL Server 2012+)
  • SQL - meaning the SSIS package is stored in MSDB database

for <ssis full location>

  • if storage type is FULL, it is the UNC path for the SSIS package file
  • if storage type is ISSERVER, it is SSIS catalog path, which we can see from SSMS when connecting to the SQL Server instance, something like the following
ssis information in ssms
  • If storage type is SQL, we can connect to the SQL Server instance and run the following query to find the SSIS package:
-- find the SSIS package inside MSDB
use msdb
select f.FolderName, [package]=p.name 
from dbo.sysssispackagefolders f
inner join dbo.sysssispackages p
on f.folderid = p.folderid;
  • We will see the following result:
ssis package information

for [/Server <server name>]

  • is only mandatory when <storage type> is not FILE, it gives the SQL Server instance name where the SSIS package is located in the SSIS catalog or MSDB.

For our inventory collection purpose, we may need the following information:

  1. Job name
  2. Step name
  3. Package location full path
  4. Package storage type
  5. Server name: where the SSIS full path resides

Here is the T-SQL code:

-- find the SSIS packages used in SQL Server Jobs
use msdb
select SQLInstance = @@ServerName
, [job]=j.name
, j.Enabled
, [step]=s.step_name
, SSIS_Package= case 
             when charindex('/ISSERVER', s.command)=1 then substring(s.command, len('/ISSERVER "\"')+1, charindex('" /SERVER ', s.command)-len('/ISSERVER "\"')-3)
             when charindex('/FILE', s.command)=1 then substring(s.command, len('/FILE "')+1, charindex('.dtsx', s.command)-len('/FILE "\"')+6)
             when charindex('/SQL', s.command)=1 then substring(s.command, len('/SQL "\"')+1, charindex('" /SERVER ', s.command)-len('/SQL "\"')-3)
             else s.command
            end
, StorageType = CASE 
             when charindex('/ISSERVER', s.command) = 1 then 'SSIS Catalog'
             when charindex('/FILE', s.command)=1 then 'File System'
             when charindex('/SQL', s.command)=1 then 'MSDB'
             else 'OTHER'
            end
, [Server] = CASE 
             when charindex('/ISSERVER', s.command) = 1 then replace(replace(substring(s.command, charindex('/SERVER ', s.command)+len('/SERVER ')+1, charindex(' /', s.command, charindex('/SERVER ', s.command)+len('/SERVER '))-charindex('/SERVER ', s.command)-len('/SERVER ')-1), '"\"',''), '\""', '')
             when charindex('/FILE', s.command)=1 then substring(s.command, charindex('"\\', s.command)+3, CHARINDEX('\', s.command, charindex('"\\', s.command)+3)-charindex('"\\', s.command)-3)
             when charindex('/SQL', s.command)=1 then replace(replace(substring(s.command, charindex('/SERVER ', s.command)+len('/SERVER ')+1, charindex(' /', s.command, charindex('/SERVER ', s.command)+len('/SERVER '))-charindex('/SERVER ', s.command)-len('/SERVER ')-1), '"\"',''), '\""', '')
             else 'OTHER'
            END
from dbo.sysjobsteps s
inner join dbo.sysjobs j
on s.job_id = j.job_id
and s.subsystem ='SSIS';
go

If running in my test environment, I get the following results:

ssis package information

Setup SSIS Package Inventory for SQL Jobs

With the script above to explore a single SQL Server instance, we can now use PowerShell to scan multiple SQL Server instances and then dump the data into an inventory table.

So, we first create a SQL Server table in a central database, let’s call it [MSSQLTips] on a repository server named [Repository].

-- inventory table for SSISPkg used in SQL Jobs
use [MSSQLTips]

drop table if exists dbo.SSISPkgForSQLJob;
go

create table dbo.SSISPkgForSQLJob (
  SQLInstance varchar(128)
, JobName varchar(256)
, IsJobEnabled bit -- to check whether the job is disabled
, StepName varchar(256)
, SSISPkgFullPath varchar(1024)
, StorageType varchar(30)
, SSISPkgServer varchar(128)
, id int identity primary key);

Here is the PowerShell script.  For the demo I am just making it a script, but in the real world I would make it an advanced function and package it into a module.

#scan sql jobs to find what SSIS packages are used.
#requires -Version 4.0
import-module SQLServer -DisableNameChecking;

#list the SQL Server instances which you want to scan jobs for SSIS pkgs
[string[]] $SQLInstances =  'server1' , 'server2\inst', 'Server3'; #change to your own list

#the t-sql is to query the sql jobs and job steps, it will run against [msdb] database
[string]$qry = @"
set nocount on;
select SQLInstance=@@ServerName
, [Job]=j.name
, [Enabled]=j.Enabled
, [Step]=s.step_name
, SSIS_Package= case when charindex('/ISSERVER', s.command)=1 then substring(s.command, len('/ISSERVER "\"')+1, charindex('" /SERVER ', s.command)-len('/ISSERVER "\"')-3)
             when charindex('/FILE', s.command)=1 then substring(s.command, len('/FILE "')+1, charindex('.dtsx', s.command)-len('/FILE "\"')+6)
             when charindex('/SQL', s.command)=1 then substring(s.command, len('/SQL "\"')+1, charindex('" /SERVER ', s.command)-len('/SQL "\"')-3)
             else s.command
            end
, StorageType = CASE WHEN charindex('/ISSERVER', s.command) = 1 then 'SSIS Catalog'
            when charindex('/FILE', s.command)=1 then 'File System'
            when charindex('/SQL', s.command)=1 then 'MSDB'
            else 'OTHER'
            end
, [Server]=CASE WHEN charindex('/ISSERVER', s.command) = 1 then replace(replace(substring(s.command, charindex('/SERVER ', s.command)+len('/SERVER ')+1, charindex(' /', s.command, charindex('/SERVER ', s.command)+len('/SERVER '))-charindex('/SERVER ', s.command)-len('/SERVER ')-1), '"\"',''), '\""', '')
            when charindex('/FILE', s.command)=1 then substring(s.command, charindex('"\\', s.command)+3, CHARINDEX('\', s.command, charindex('"\\', s.command)+3)-charindex('"\\', s.command)-3)
            when charindex('/SQL', s.command)=1 then replace(replace(substring(s.command, charindex('/SERVER ', s.command)+len('/SERVER ')+1, charindex(' /', s.command, charindex('/SERVER ', s.command)+len('/SERVER '))-charindex('/SERVER ', s.command)-len('/SERVER ')-1), '"\"',''), '\""', '')
            else 'OTHER'
                END
from dbo.sysjobsteps s
inner join dbo.sysjobs j
on s.job_id = j.job_id
and s.subsystem ='SSIS';

"@;

$dt = new-object system.data.datatable;

$c = new-object System.Data.DataColumn ('SQLInstance', [system.string]);
$dt.columns.Add( $c);
$c = new-object System.Data.DataColumn ('JobName', [system.string]);
$dt.columns.Add( $c);
$c = new-object System.Data.DataColumn ('IsJobEnabled', [system.int16]);
$dt.columns.Add( $c);
$c = new-object System.Data.DataColumn ('StepName', [system.string]);
$dt.columns.Add( $c);
$c = new-object System.Data.DataColumn ('SSISPkgFullPath', [system.string]);
$dt.columns.Add( $c);
$c = new-object System.Data.DataColumn ('StorageType', [system.string]);
$dt.columns.Add( $c);
$c = new-object System.Data.DataColumn ('SSISPkgServer', [system.string]);
$dt.columns.Add( $c);

#loop through each SQL Instance and check their jobs for SSIS packages
foreach ($svr in $SQLInstances)
{
    $rslt = invoke-sqlcmd -ServerInstance $svr -Database msdb -Query $qry -QueryTimeout 1200 -as DataRows;

    if ($rslt.count -gt 0) 
    {
     $rslt | % { $r = $dt.NewRow(); 
             $r.SQLInstance = $_.SQLInstance; 
             $r.JobName = $_.Job;
             $r.IsJobEnabled = $_.Enabled;
             $r.StepName = $_.Step;
             $r.SSISPkgFullPath = $_.SSIS_Package;
             $r.StorageType = $_.StorageType;
             $r.SSISPkgServer = $_.Server;
             $dt.rows.add($r);
             }
    }
}

#write to [Repository] central server / database, change Repository server and mssqltips db name
#to your own
Write-SqlTableData -server ‘Repostiory’ -database mssqltips -table SSISPkgForSQLJob -SchemaName dbo -InputData $dt ;

Assuming you have the SQL Server PowerShell module installed, you can start PowerShell ISE and open a new window then copy and paste the PowerShell script from above into the window. Then make the necessary changes, such as $SQLInstances variable values, and your repository server / database names, then you can just run the PS script and see what you get.

In my environment, I get the following (Note: I purposely removed sensitive info from the image):

ssis package information

Summary

In this tip, we walked through how to collect information about SSIS packages used in SQL Server Agent Jobs. With this information, we can better manage SQL Server Jobs and SSIS packages during the life cycle of object modification, migration, cleanup or an upgrade.

Especially with SSIS packages stored in the SSIS catalog (a feature available since SQL Server 2012), when a job which has a job step containing an SSIS package and the step fails, it would be very useful to know the SSIS package location if we want to automatically find the package failure message.

Next Steps

You can modify the inventory collection script and table to get more information, such as SSIS package default parameter information, which is included in job step command with the /par parameter.

You can also review the following resources to better test and understand this tip.



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, May 20, 2020 - 1:15:43 PM - jeff_yao Back To Top (85709)

@Vikky, thanks for reading the tip. Regarding extracting /PAR values, that is not the purpose of this tip. However to retrieve /PAR values, you may need to parse the job step command, and go from the first /PAR to the last /PAR, it is a string parsing task.

Wednesday, May 20, 2020 - 6:29:18 AM - Vikky Back To Top (85706)

Hi Jeffery,

I am trying to get details of default parameters but couldnt get exact output as you showed. Can you please help me out with getting /Par details of all parameters. I do not have much knowledge in scripting and I tried several ways with the above script and there is no luck. Help would be really appreciated. 














get free sql tips
agree to terms