Check multiple table usage in SSIS packages with Powershell

Technical Value

In my last post about checking table usage in SSIS packages with Powershell I explained how to use Powershell to find out the SSIS packages that access one table (or any other object like a view or a stored procedure). With this post I'll extend the solution to check for usage of several tables at the same time.

Why would you want to check more than one table at the same time?

Maybe you have a big change in your existing system where you have to rename more than one table or maybe you want to move several existing tables to a new schema. Let's imagine that you have to move the staging tables Dim.Dim01, Dim.Dim02 and Dim.Dim03 to a new schema and you have the following SSIS Projects: CheckMultipleTabs01 You execute the script from the last blog post for every table and see the results for every table: CheckMultipleTabs02 We already see that there are some overlappings for the different tables. To get a better overview of which package accesses which table (you probably want to open every SSIS package only one time and not for every table) you could now merge the results manually to one new overview. In this example this is easy. But the more tables you have to check the more manual work you'll have. And the more manual work you have, the more mistakes you might make.

How can I provide more than one table name?

There are several possibilities to do that, my solution is to use a CSV-file. This helps you structure the input data and it is very easy to maintain, you can do it with Excel, a text editor or even automatically with SSIS. The CSV-file should at least contain the schema name and the table name, but you could also add some other helpful information like the object type if you want. In my example this is what the basic CSV-file looks like: CheckMultipleTabs03

Parameters for the Powershell-script

For this script I propose two parameters. One parameter is for the local folder where copies of your SSIS packages are stored. The other parameter is for the name of the input CSV file. Read the CSV file This is the easy part, use the Import-CSV cmdlet to create a Powershell object that has the appropriate structure and holds all the information.

How to define the search criteria?

As I already explained in my last post we have more than one way to write the name of a table, so we created an array that holds the different ways to write the name of a table. But this time we search for more than one table name so for every table we need such an array. We could create this array every time during processing, but that would mean that we recreate it for every new package and every new table. In the example where we search for 3 tables in 25 packages we would do this 75 times. Of course we could create one big array that contains all the different spellings for all the different tables that we are searching for instead. But this also means that if we have a match then we only know that any of the tables we are searching for is accessed by the SSIS package. For me this is too vague, I prefer to know which table or tables are accessed by the SSIS package. So my solution is to create a custom object $SearchList that combines the information from the CSV object with the array with the different ways to write the name of the table: CheckMultipleTabs04

Search for matches and create the output

Now use a ForEach loop to iterate over all your SSIS package files using Get-Content and use a nested ForEach loop that iterates over the $SearchList object to check for every table if it is used in the package or not. Of course do not forget to create a readable output, maybe also put it into a text file. This is the output of the new script for the CSV-file above on the console. CheckMultipleTabs05 We directly see that some packages access more than one table, so you immediately know all the tables you should look for when you open the package in Visual Studio.

#Declare variables
param (
# Required parameter
[string]$LocalPath = $(Read-Host "Local Path"),
[string]$CsvFileName = $(Read-Host "CSV-file name")
)
# Add a backslash to $LocalPath if missing
if (-not $LocalPath.EndsWith("\"))
{
$LocalPath = $LocalPath + "\"
}
# Set dependend values
[string]$ResultsFile = $LocalPath+"ResultsOfCheck.txt"
[string]$CsvFilePath = $LocalPath+$CsvFileName
# Import Csv-File that contains the tables to search for
$ListOfTables = Import-CSV -Delimiter ';' $CsvFilePath
# Create the empty search list
$SearchList=@()
# Define SearchListItemProperties of a search list item
$SearchListItemProperties = @{TableInfo = ''; SearchStrings = ''}
$ItemTemplate = New-Object -TypeName PSObject -Property $SearchListItemProperties
# Create the search list from the list tables
$ListOfTables |
ForEach-Object {
# Prepare the searchstrings to search for several spellings like Dim.Dim1, [Dim].Dim1 etc.
$SearchStrings = New-Object System.Collections.ArrayList($null)
$SearchStrings.Add($_.SchemaName + "." + $_.TableName) | Out-Null
$SearchStrings.Add("[" + $_.SchemaName + "]." + $_.TableName) | Out-Null
$SearchStrings.Add($_.SchemaName + ".[" + $_.TableName + "]") | Out-Null
$SearchStrings.Add("[" + $_.SchemaName + "].[" + $_.TableName + "]") | Out-Null
# If table schema is dbo then additionally search only for the table name
if ($_.SchemaName -eq "dbo")
{
$SearchStrings.Add($_.TableName) | Out-Null
}
# Create the new SearchList item which contains information from list of tables and the search strings
$NewListItem = $ItemTemplate.PSObject.Copy()
$NewListItem.TableInfo = $_
$NewListItem.SearchStrings = $SearchStrings
$SearchList += $NewListItem
}
# Write heading to file and to console
$Output = "Packages with hits: "
Write-Host $Output
$Output > $ResultsFile
# Get a list of all dtsx packages that should be checked
$SsisPackages = Get-Childitem $LocalPath *.dtsx -Recurse
# Loop through all packages
ForEach ($SsisPackage In $SsisPackages)
{
# Variable for nice output
$FirstHitInPackage = $True
ForEach ($SearchListItem In $SearchList)
{
# Check if package uses a table
$ContainsTableNames = Select-String -path $SsisPackage.FullName -pattern $SearchListItem.SearchStrings -SimpleMatch -quiet
# Output to console and file
If ($ContainsTableNames -eq $True)
{
# Print name of package if this is the first hit for the package
If ($FirstHitInPackage -eq $True)
{
$FirstHitInPackage = $False
$Output = " - " + $SsisPackage.FullName.Replace($LocalPath,"").TrimStart("\")
Write-Host $Output
$Output >> $ResultsFile
}
# Print name of the table
$Output = " - " + $SearchListItem.TableInfo.SchemaName + "." + $SearchListItem.TableInfo.TableName
Write-Host $Output
$Output >> $ResultsFile
}
}
}

Can I use this also for other object types?

Of course you can use this script also for views, stored procedures and other objects that can be accessed by SSIS. In this case I recommend to add the object type to the CSV-file and add this object type to the output to improve readability of the output.