By: Jeffrey Yao | 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:
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:
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
- 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:
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:
- Job name
- Step name
- Package location full path
- Package storage type
- 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:
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):
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 Integration Services (SSIS) Tutorial
- Create a SQL Server Integration Services SSIS Project
- Analyzing SQL Agent Job and Job Step History in SQL Server
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips