By:Jeffrey Yao | Last Updated: 2018-12-05 || Related Tips:More > 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:


Automated Inventory Collection of Scheduled SQL Server Integration Services Pack ...
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 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 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
Automated Inventory Collection of Scheduled SQL Server Integration Services Pack ...
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:
Automated Inventory Collection of Scheduled SQL Server Integration Services Pack ...
for [/Server ] 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.commandend
, 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:


Automated Inventory Collection of Scheduled SQL Server Integration Services Pack ...
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

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Automated Inventory Collection of Scheduled SQL Server Integration Services Pack ...
本站链接:https://www.codesec.net/view/621019.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(58)