SSIS table usage – how to find which tables are used in which packages


When you have big SSIS projects it’s often problematic to have full control over which database tables are in use in which packages. So, to figure it out I created this “little” script that collects the names of every table in your database of choice, then searches through an entire directory (with subdirectories) for dtsx-files that contain this table name. It also runs somewhat fast; a total of 257 table names was matched with roughly 300 dtsx-files in 16 different folders in around 45 seconds. Export the results to Excel, add column header filters and you have a nice documentation of your table usage in a jiffy!

/*
Script to find table/view usage inside SSIS packages.
Will loop through every single table/view in the current database 
and find which SSIS-packages they are used in.
*/
 
 
SET NOCOUNT ON
GO
 
--> Database to search
USE MyDatabase
GO
 
--> Add name of folder containing .dtsx-files to search
DECLARE @DtsxFolder varchar(255) = 'C:\Temp\ssis\'
 
DECLARE 
 @cmd varchar(2000),
 @Table_Catalog varchar(255), 
 @Table_Schema varchar(255), 
 @Table_Name varchar(255), 
 @Table_Type varchar(255)
 
DECLARE @cmd_table table (
 id int identity(1, 1) not null, 
 cmd_output varchar(255)
 )
DECLARE @results_table table (
 id int identity(1, 1) not null, 
 Table_Catalog varchar(255), 
 Table_Schema varchar(255), 
 Table_Name varchar(255), 
 Table_Type varchar(255), 
 DtsxFile varchar(255)
 )
 
DECLARE cur CURSOR FOR 
 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_SCHEMA, TABLE_NAME
 
OPEN cur
FETCH NEXT FROM cur INTO @Table_Catalog, @Table_Schema, @Table_Name, @Table_Type
 
WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @cmd = 'findstr /M /s /C:"' + @Table_Name + '" '+ @DtsxFolder + '*.dtsx' --> Add /s to search subfolders
 
 INSERT INTO @cmd_table
 EXEC xp_cmdshell @cmd
 
 INSERT INTO @results_table
 (Table_Catalog, Table_Schema, Table_Name, Table_Type, DtsxFile)
 SELECT @Table_Catalog, @Table_Schema, @Table_Name, @Table_Type, cmd_output
 FROM @cmd_table
 WHERE cmd_output IS NOT NULL
 
 DELETE @cmd_table
 
 FETCH NEXT FROM cur INTO @Table_Catalog, @Table_Schema, @Table_Name, @Table_Type
 END
 
CLOSE cur
DEALLOCATE cur
 
SELECT * FROM @results_table
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s