Looking for references in SSIS

This post was originally created in February of 2012 but for some reason it got deleted by Word Press with no notice or explanation. I just discovered it missing and though I can’t find a way to give the post its original URL (https://rickbielawski.wordpress.com/2012/02/22/looking-for-references-in-ssis), with the help of web.archive.org I’ve been able to restore the content.


Similar to my post of a few years ago where you may want to search an SSRS package (Querying Information from Deployed SSRS Packages) you can also search SSIS. The big difference is in the namespaces used and the fact that sometimes the package isn’t actually in a database to be queried. This query finds certain types of information in a package and returns its location.

-- This query shows exactly where in a package a query containing the search criteria (@F) can be found. 
-- Be careful in your specification of table/column names since wildcards and patterns are not allowed 
-- and some references are generated as [schema].[name] while others are just schema.name.

Declare @F varchar(100) = 'TextToFind';  --< Put the text you want to find here

With XMLNAMESPACES(N'www.microsoft.com/SqlServer/Dts'               AS DTS
                  ,N'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask
                  ,N'http://www.w3.org/2001/XMLSchema'              AS xsd
                  ,N'urn:schemas-microsoft-com:xml-msdata'          AS msdata)
,   FileData AS (select cast( [SSISPkgData].BulkColumn AS XML) AS pkgXML
                   from openrowset(bulk 'H:\SomePackage.dtsx',single_blob) as [SSISPkgData])
, Found as (                                 
select Nodes.value('@SQLTask:SqlStatementSource','varchar(MAX)') Txt
      -- Notice this very cool feature.  From SSMS results window you can click on the statement to see it. 
      ,QueryText  =(select [processing-instruction(q)]=Nodes.value('@SQLTask:SqlStatementSource'
                    ,'varchar(MAX)') for xml path(''),type)
      ,Nodes.value('local-name(../..)','varchar(max)') Parent
      ,Nodes.value('../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') ParentName
      ,Nodes.value('local-name(../../..)','varchar(max)') GrandParent
      ,Nodes.value('../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GrandParentName
      ,Nodes.value('local-name(../../../..)','varchar(max)') GGParent
      ,Nodes.value('../../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GGParentName
  from FileData F
 CROSS APPLY F.pkgXML.nodes('//SQLTask:SqlTaskData[contains(upper-case(@SQLTask:SqlStatementSource)
                            ,upper-case(sql:variable("@F")))]') Pkg(Nodes)
union all
select Nodes.value('.','varchar(MAX)') Txt
      ,QueryText  =(select [processing-instruction(q)]=Nodes.value('.','varchar(MAX)') for xml path(''),type)
      ,Nodes.value('local-name(..)','varchar(max)') Parent
      ,Nodes.value('../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') ParentName
      ,Nodes.value('local-name(../..)','varchar(max)') GrandParent
      ,Nodes.value('../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GrandParentName
      ,Nodes.value('local-name(../../..)','varchar(max)') GGParent
      ,Nodes.value('../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GGParentName
  from FileData F
 CROSS APPLY F.pkgXML.nodes('//DTS:Property[@DTS:Name="Expression" and . != "" and contains(upper-case(.)
                            ,upper-case(sql:variable("@F")))]') Pkg(Nodes)
union all
select Nodes.value('.','varchar(MAX)') Txt
      ,QueryText  =(select [processing-instruction(q)]=Nodes.value('.','varchar(MAX)') for xml path(''),type)
      ,Nodes.value('local-name(../..)','varchar(max)') Parent
      ,Nodes.value('../../@name','varchar(MAX)') ParentName
      ,Nodes.value('local-name(../../../../../..)','varchar(max)') GrandParent
      ,Nodes.value('../../../../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GrandParentName
      ,Nodes.value('local-name(../../../../../../..)','varchar(max)') GGParent
      ,Nodes.value('../../../../../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GGParentName
  from FileData F
 CROSS APPLY F.pkgXML.nodes('//property[(@name="SqlCommand" or @name="SqlCommandVariable" or 
                             @name="OpenRowset" or @name="OpenRowsetVariable") and . != "" and 
                             contains(upper-case(.),upper-case(sql:variable("@F")))]') Pkg(Nodes)
)
Select Parent,ParentName,GrandParent,GrandParentName,GGParent,GGParentName,QueryText
  from Found
 --where Txt like '%update%'  -- Uncomment this Where clause to further restrict results

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: