SSIS: Finding Packages having References to a Table or Column
Introduction
There was a question in the forums recently asking whether its possible to find out the SSIS packages that referred a particular table or column.
This requirement boils down to the business cases where there is any metadata change happening in the db and we need to identify impact of the changes on the ETL system consisting of SSIS packages.
This article discusses a method which you can use to search within the SSIS packages residing in a project folder (file system) for a particular column reference.
Base Logic
The base logic for this method is based on the fact that SSIS code behind is actually a XML value and all components of package will be represented as XML node values within it. This XML can be parsed to identify if it has a reference for our searched table or column. This can be done as per the code below
01.declare @t table
02.(
03.x xml
04.)
05.
06.declare @ColumnName varchar(100) = <Your Column Name To be Searched>
07.
08.INSERT @t
09.SELECT *
10.FROM OPENROWSET(BULK '<Full Package Path>',
11. SINGLE_BLOB) AS x;
12.
13.
14. WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS)
15. SELECT t.u.value('.','varchar(100)')
16. FROM @t
17. CROSS APPLY x.nodes('/DTS:Executable/DTS:Property[@DTS:Name="ObjectName"]')t(u)
18. WHERE x.exist('//inputColumn[@name=sql:variable("@ColumnName")]') = 1
19. OR x.exist('//externalMetadataColumn[@name=sql:variable("@ColumnName")]') = 1
20. OR x.exist('//outputColumn[@name=sql:variable("@ColumnName")]') = 1
This will give you the names of packages which has references to the column being searched within the package body.
The Reusable Procedure
This method can be extended to search a set of packages stored in a folder to determine the packages dependent on the table or the column being searched.
You can make this into a procedure as below
CREATE PROC ListObjectDependentSSISPackages
@FolderPath varchar(1000),
@ObjectName varchar(100),
@ObjectType varchar(10)
AS
IF OBJECT_ID('DTSPackages') IS NOT NULL
DROP TABLE DTSPackages;
CREATE TABLE DTSPackages
(
x xml
)
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;
CREATE TABLE #DirectoryTree (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit);
INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @FolderPath,1,1;
DECLARE @Path varchar(100),@ID int
SELECT TOP 1 @Path = subdirectory,@ID = id
FROM #DirectoryTree
WHERE isfile = 1
AND subdirectory LIKE '%.dtsx'
ORDER BY id
WHILE @Path IS NOT NULL
BEGIN
DECLARE @SQL varchar(max) = 'INSERT DTSPackages
SELECT *
FROM OPENROWSET(BULK ''' + @FolderPath + '\' + @Path + ''',
SINGLE_BLOB) AS x;'
EXEC (@SQL)
SET @Path = NULL
SELECT TOP 1 @Path = subdirectory,@ID = id
FROM #DirectoryTree
WHERE isfile = 1
AND subdirectory LIKE '%.dtsx'
AND id > @ID
ORDER BY id
END;
IF @ObjectType = 'Column'
BEGIN
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS)
SELECT t.u.value('.','varchar(100)')
FROM DTSPackages
CROSS APPLY x.nodes('/DTS:Executable/DTS:Property[@DTS:Name="ObjectName"]')t(u)
WHERE x.exist('//inputColumn[@name=sql:variable("@ObjectName")]') = 1
OR x.exist('//externalMetadataColumn[@name=sql:variable("@ObjectName")]') = 1
OR x.exist('//outputColumn[@name=sql:variable("@ObjectName")]') = 1
OR x.exist('//DTS:Variable/DTS:VariableValue[contains(.,sql:variable("@ObjectName"))]')=1
END
IF @ObjectType = 'Table'
BEGIN
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS,'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
SELECT t.u.value('.','varchar(100)')
FROM DTSPackages
CROSS APPLY x.nodes('/DTS:Executable/DTS:Property[@DTS:Name="ObjectName"]')t(u)
WHERE
x.exist('//SQLTask:SqlTaskData[contains(./@SQLTask:SqlStatementSource,sql:variable("@ObjectName"))]')=1
OR x.exist('//property[@name="SqlCommand" and contains(.,sql:variable("@ObjectName"))]')=1
OR x.exist('//DTS:Variable[@DTS:Name="Expression" and contains(.,sql:variable("@ObjectName"))]')=1
OR x.exist('//DTS:Variable/DTS:VariableValue[contains(.,sql:variable("@ObjectName"))]')=1
END
GO
Invoke it as below and you will get details of all packages in the folder that has a reference to the table or the column you passed
EXEC ListObjectDependentSSISPackages '<Folder Path>','<Column Name>','Column'
List Directory Contents
The above procedure makes use of xp_dirtree extended procedure to list the files from a given directory path. The extended procedure takes as input a directory path, a depth value and a file indicator bit value. It will give us output a listing of the files as well as sub-folders within the directory and will also return the depth information of the folder/file within the directory passed. This result can be populated to a table which shall then be iterated to parse each SSIS files (.dtsx) for identifying the passed object references.