如何查询 Integration Services 目录 [英] How to Query Integration Services Catalog

问题描述

问题:识别连接到 SQL Server 上特定数据库表的所有 SSIS 包.

Problem: Identify all SSIS packages that connect to a specific database table on SQL Server.

详细信息:服务器上部署了近100个包，大多数包体积庞大，因此很难高精度地手动通过它们.

Details: There are almost 100 packages deployed to the server, most packages are huge in size, so it will be difficult to go through them manually with a high degree of accuracy.

有没有一种快速且自动化的方法来做到这一点?

Is there a fast and automated way to do this?

使用 SQL Server 的潜在解决方案:

Potential solution using SQL Server:

1. 查询 Integration Services 目录并检索 *.dtsx 包
2. 将包数据加载到 XML 列/数据类型中
3. 解析/查询包以获取对特定数据库表的任何引用

也许 C# 应用程序也可以这样做?

Perhaps a C# application may do the same?

我非常感谢能提供的任何帮助.

I greatly appreciate any assistance that can be afforded.

推荐答案

感谢 Ms SQL Girl: http://www.mssqlgirl.com/editing-published-ssis-package-in-sql-server-2012.html

Credit goes to Ms SQL Girl: http://www.mssqlgirl.com/editing-published-ssis-package-in-sql-server-2012.html

USE SSISDB

SELECT pr.name AS [ProjectName]
    , pr.description AS [ProjectDescription]
    , pr.last_deployed_time AS [ProjectLastValidated]
    , pr.validation_status AS [ProjectValidationStatus]
    , op.object_name AS [PackageName]
    , op.design_default_value AS [DefaultConnectionString]
FROM [internal].[object_parameters] op
INNER JOIN [internal].[projects] pr
ON pr.project_id = op.project_id
AND pr.object_version_lsn = op.project_version_lsn
WHERE op.parameter_name LIKE '%.ConnectionString'

这篇关于如何查询 Integration Services 目录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！