CODEWITHSUNDEEP
ssispackage
user6279935
user6279935

Reputation: 15

Find out the SSIS packages that are dependent on a given table

Is there any way by which I can get the list of SSIS packages that are dependent on a given table?

I have about 100+ SSIS packages and I don't know which table is related to which package. It is not feasible for me to open each package and search for the required table.

Upvotes: 1

Views: 2514

Answers (2)

mob43059
mob43059

Reputation: 31

Simple search through all files in a SSIS project:

Open the Solution in SSDT/Visual Studio and type CTRL+SHIFT+F

Then type the name of the table/view. You will get all objects with dependencies on this table/view, including SSIS packages using it.

Upvotes: 0

Yousuf Sultan
Yousuf Sultan

Reputation: 3257

You can try out this query:

Select 
SSIS.name As PackageName 
,SSIS.description As PackageDescription 
,SSIS.createdate As PackageCreateDate  
,SSIS.ownersid 
,(Case SSIS.packagetype
    when 0 then 'Undefined'
    when 1 then 'SQL Server Import and Export Wizard'
    when 2 then 'DTS Designer in SQL Server 2000'
    when 3 then 'SQL Server Replication'
    when 5 then 'SSIS Designer'
    when 6 then 'Maintenance Plan Designer or Wizard'
End) As PackageType 
,(Case SSIS.packageformat
    when 0 then 'SSIS 2005 version'
    when 1 then 'SSIS 2008 version'
End) As PackageFormat 
,CAST(CAST(SSIS.packagedata AS VARBINARY(MAX)) AS XML) PackageXML
From 
[msdb].[dbo].[sysssispackages] As SSIS

I tried this and I got the list for my issue.

Upvotes: 1

Related Questions