In BI projects you sometimes want to rename a database table or move a database table to a different database schema. There are several reasons why you might want to do this, the rules for naming objects may change, maybe you have to move an existing table to a legacy schema or you simply have to correct a table name in a running system. The bigger your BI solution is, the more dependencies you’ll have and you of course have to make sure that everything runs properly after your change.
Ensuring that the database itself remains consistent is quite simple, because SSDT provides a refactoring functionality to make sure that dependent objects like views, stored procedures, constraints etc. are changed automatically to use the new table. Unfortunately this refactoring function is not available for SSIS packages and in BI projects you have dozens or hundreds of SSIS packages which still have to run after you have modified the table. You somehow have to find all packages that you need to change. How can you do that?
1st shot: Check everything in Visual Studio As always one option is opening package after package in Visual Studio and search for the table name in all tasks or in the code of the package. Actually the more packages you have the more time you n
eed and you will probably miss some parts where the tables are used.
2nd shot: Reduce the number of packages
Looking through all the packages is too much work and unreliable, so you have to reduce the number of packages that you have to check. You can do this e.g. by deciding to check only packages with package names that match a certain pattern. For a DimVerySpecificAndOnlyRelatedToOneFact dimension table this could work, but if you change the date dimension table I’m sure that you will miss some packages that use that table. So how else can we reduce the number of packages that we have to open, check and edit in Visual Studio?
3rd shot: Search through the code of all packages with Powershell
I recommend to write a Powershell script that searches for your table in all SSIS packages and tells you which packages use the table and need to be checked and maybe edited by you. What do we need to make this script run? Parameters Add a parameter to tell your script where you have the copies of your SSIS packages (e.g. the local folder from your source control tool) and add parameters for TableSchema and TableName to have a script that can be reused.
Define the search criteria
What makes the search a little bit difficult is that there are several possibilities how a table name is stored in the SSIS package. For a „Dim.DimDate“ there are at least 5 ways I have seen in projects: Dim.DimDate, [Dim].DimDate, Dim.[DimDate] and [Dim].[DimDate]. And if table schema is „dbo“ then it could be simply DimDate without the schema name. Looping through all the packages and searching for every string separately (means 4 to 5 times more loops) does not look performant to me. Fortunately Select-String also works with arrays, so let’s create an array that contains all the spellings.
Perform the search
In a ForEach loop now use Get-Content and Select-String -pattern YourArrayWithSearchStrings -SimpleMatch to loop trough every package once and look for all spellings at the same time. Here it also is important to add -SimpleMatch to make sure the script only finds the exact matches. With the list of packages you now know which packages you have to check and change, but you also know which packages do not need to be changed.
-
#Declare variables
-
param (
-
# Required parameter
-
[string]$LocalPath = $(Read-Host „Local Path“),
-
[string]$TableSchema = $(Read-Host „TableSchema“),
-
[string]$TableName = $(Read-Host „TableName“)
-
-
)
-
# Add a backslash to SLocalPathParent if missing
-
if (-not $LocalPath.EndsWith(„\“))
-
{
-
$LocalPath = $LocalPath + „\“
-
}
-
-
# Define the local output file
-
[string]$ResultsFile = $LocalPath+„\ResultsOfCheck.txt“
-
-
# Prepare the searchstrings to search for several spellings like Dim.Dim1, [Dim].Dim1 etc.
-
$SearchStrings = New-Object System.Collections.ArrayList($null)
-
$SearchStrings.Add($TableSchema + „.“ + $TableName) | Out-Null
-
$SearchStrings.Add(„[„ + $TableSchema + „].“ + $TableName) | Out-Null
-
$SearchStrings.Add($TableSchema + „.[„ + $TableName + „]“) | Out-Null
-
$SearchStrings.Add(„[„ + $TableSchema + „].[„ + $TableName + „]“) | Out-Null
-
-
# If table schema is dbo then additionally search only for the table name
-
if ($TableSchema -eq „dbo“)
-
{
-
$SearchStrings.Add($TableName) | Out-Null
-
}
-
-
# Write heading to file and to console
-
$Output = „Packages that access table „ + $TableSchema + ‚.‘ + $TableName + ‚:‘
-
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)
-
{
-
# Check if package uses a table
-
$ContainsDeletedTableNames = Get-Content $SsisPackage.FullName | Select-String -pattern $SearchStrings -SimpleMatch -quiet
-
-
# Output to console and file
-
If ($ContainsDeletedTableNames -eq $True)
-
{
-
$Output = “ – „ + $SsisPackage.FullName.Replace($LocalPath,„“).TrimStart(„\“)
-
Write-Host $Output
-
$Output >> $ResultsFile
-
}
-
}
Three final remarks
1. I did not consider using views or synonyms to solve this task. Although this could mean that you don’t have to change your SSIS packages, I prefer to not use views or synonyms for such tasks. They make your system even more complex than it already is, because now you have different aliases for the same table and in the long term nobody will understand how the SSIS packages, views synonyms are tables work together.
2. Probably you come around more ways to write a table name than those 5 I mention in the post. If you have more, please let me know.
3. There are of course other cases where this Powershell script might help. If you change table columns or if you rename a stored procedure you will also want to know which SSIS packages use those objects. But the renaming of a table and one SSIS package that we did not expect to use that table crashing during first execution led to this blog post.
Kommentare (0)