3

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

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?

Potential solution using SQL Server:

  1. Query the Integration Services Catalog and retrieve the *.dtsx package
  2. Load the package data into an XML column/data type
  3. Parse/query the package for any reference to the specific database table

Perhaps a C# application may do the same?

I greatly appreciate any assistance that can be afforded.

J Weezy
  • 3,507
  • 3
  • 32
  • 88
  • I'd suggest trying the potential solution and asking a question if you run into an issue doing that. Right now your question is too broad for Stack Overflow, which primarily focuses on specific code-related issues. – economy Dec 04 '15 at 22:30

2 Answers2

4

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'
J Weezy
  • 3,507
  • 3
  • 32
  • 88
0

I would open the Visual Studio project containing all these packages (using VS), and then use the VS "Find in Files" function to search for the table name. It is quite quick, and using the "Match whole word" search should pop up just what you want.

If you don't already have a Visual Studio project/solution containing these packages, you probably should download them and build one anyway, so they can be supported/maintained.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • Sorry for the late response. I ultimately found the solution to the problem that spawned the question on this forum. At any rate, a query was proposed on a separate blog (see answer above). – J Weezy Mar 17 '16 at 17:11