06.07.2016

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.

  1. #Declare variables
  2. param (
  3.   # Required parameter
  4.   [string]$LocalPath = $(Read-Host "Local Path"),
  5.   [string]$CsvFileName = $(Read-Host "CSV-file name")
  6. )
  7. # Add a backslash to $LocalPath if missing
  8. if (-not $LocalPath.EndsWith("\"))
  9. {
  10.   $LocalPath = $LocalPath + "\"
  11. }
  12.  
  13. # Set dependend values
  14. [string]$ResultsFile = $LocalPath+"ResultsOfCheck.txt"
  15. [string]$CsvFilePath = $LocalPath+$CsvFileName
  16.  
  17. # Import Csv-File that contains the tables to search for
  18. $ListOfTables = Import-CSV -Delimiter ';' $CsvFilePath
  19.  
  20. # Create the empty search list
  21. $SearchList=@()
  22.  
  23. # Define SearchListItemProperties of a search list item
  24. $SearchListItemProperties = @{TableInfo = ''; SearchStrings = ''}
  25. $ItemTemplate = New-Object -TypeName PSObject -Property $SearchListItemProperties
  26.  
  27. # Create the search list from the list tables
  28. $ListOfTables |
  29.   ForEach-Object {
  30.     # Prepare the searchstrings to search for several spellings like Dim.Dim1, [Dim].Dim1 etc.
  31.     $SearchStrings = New-Object System.Collections.ArrayList($null)
  32.     $SearchStrings.Add($_.SchemaName + "." + $_.TableName) | Out-Null
  33.     $SearchStrings.Add("[" + $_.SchemaName + "]." + $_.TableName) | Out-Null
  34.     $SearchStrings.Add($_.SchemaName + ".[" + $_.TableName + "]") | Out-Null
  35.     $SearchStrings.Add("[" + $_.SchemaName + "].[" + $_.TableName + "]") | Out-Null
  36.    
  37.     # If table schema is dbo then additionally search only for the table name
  38.     if ($_.SchemaName -eq "dbo")
  39.     {
  40.       $SearchStrings.Add($_.TableName) | Out-Null
  41.     }
  42.    
  43.     # Create the new SearchList item which contains information from list of tables and the search strings
  44.     $NewListItem = $ItemTemplate.PSObject.Copy()
  45.     $NewListItem.TableInfo = $_
  46.     $NewListItem.SearchStrings = $SearchStrings
  47.     $SearchList += $NewListItem
  48.   }
  49.  
  50. # Write heading to file and to console
  51. $Output = "Packages with hits: "
  52. Write-Host $Output
  53. $Output  > $ResultsFile
  54.  
  55. # Get a list of all dtsx packages that should be checked
  56. $SsisPackages = Get-Childitem $LocalPath *.dtsx -Recurse
  57.  
  58. # Loop through all packages
  59. ForEach ($SsisPackage In $SsisPackages)
  60. {
  61.   # Variable for nice output
  62.   $FirstHitInPackage = $True
  63.  
  64.   ForEach ($SearchListItem In $SearchList)
  65.   {
  66.     # Check if package uses a table
  67.     $ContainsTableNames = Get-Content $SsisPackage.FullName | Select-String -pattern $SearchListItem.SearchStrings -SimpleMatch -quiet    
  68.    
  69.     # Output to console and file
  70.     If ($ContainsTableNames -eq $True)
  71.     {
  72.         # Print name of package if this is the first hit for the package
  73.         If ($FirstHitInPackage -eq $True)
  74.       {
  75.         $FirstHitInPackage = $False
  76.         $Output = " - " + $SsisPackage.FullName.Replace($LocalPath,"").TrimStart("\")
  77.         Write-Host $Output
  78.         $Output >> $ResultsFile    
  79.       }
  80.        
  81.         # Print name of the table
  82.       $Output = "     - " + $SearchListItem.TableInfo.SchemaName + "." + $SearchListItem.TableInfo.TableName
  83.       Write-Host $Output
  84.       $Output >> $ResultsFile
  85.     }
  86.   }    
  87. }

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.

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden