My client asked me to make an overview of packages that use stored procedures from a certain database. Of course I can open each package and edit each task/transformation that can use stored procedures. But they have dozens of SSIS solutions with each multiple projects and a lot of packages.
The overview will probably be outdated before I can finish reviewing all packages. Is there a smarter, faster and less tedious solution?
Solution
PowerShell to the rescue! I made a small script that loops through all packages in a certain folder and then searches for certain strings in the source code of the packages. Those search strings are in an array that can be filled manually or automatically with a script that gets all stored procedures from a database.
#PowerShell: Example 1 with hardcoded list of stored procedures
# Where are all my packages
$PackagePath = "H:\My Documents\Visual Studio 2013\projects\TFS"
# Where should we save the report
$ReportPath = "H:\My Documents\packagestoredprocedures.csv"
# Which stored procedures do we need to find
$StoredProcedures = @("pr_Forced_append","pr_DV_HUB","pr_DV_LINK")
#################################################################################################
# No need to edit below this line
#################################################################################################
# Declare array to store result in
$PackageSPArray = @("Solution;Project;Package;StoredProcedure")
# Get all SSIS packages in the configured folder and in all its subfolders
Get-ChildItem $PackagePath -Filter “*.dtsx” -Recurse | Where-Object { $_.Attributes -ne “Directory”} |
ForEach-Object {
# Ignore packages in the object folder
If (!$_.FullName.Contains("\obj\"))
{
# Read entire package content like it's a textfile
$PackageXml = Get-Content $_.FullName
# Loop through Stored Procedure array
ForEach ($StoredProcedure in $StoredProcedures)
{
# Optionally write search to screen
# Write-Host ("Looking for " + $StoredProcedure + " in package " + $_.Name)
# Check if it contains the stored procedure
If ($PackageXml | Select-String -Pattern $StoredProcedure)
{
# Optionally write find to screen
# Write-Host ("Found " + $StoredProcedure.ToString() + " in package " + $_.Name.ToString())
# Filling array: Solution;Project;Package;StoredProcedure
$PackageSPArray += $_.Directory.Parent.Name + ";"+ $_.Directory.Name + ";" + $_.Name + ";" + $StoredProcedure
}
}
}
}
# Optionally write result to screen
# $PackageSPArray | ForEach-Object {$_}
# Write result to file
$PackageSPArray | % {$_} | Out-File $ReportPath
#PowerShell: Example 2 with query to get stored procedures
# Where are all my packages
$PackagePath = "H:\My Documents\Visual Studio 2013\projects\TFS"
# Which server do we connect to to find stored procedures
$SqlServer = "MyServer\SQL2014"
# Which database do we connect to to find stored procedures
$Database = "DWH"
# Where should we save the report
$ReportPath = ("H:\My Documents\packagestoredprocedures" + $Database.ToString() + ".csv")
#################################################################################################
# No need to edit below this line
#################################################################################################
# Query database to find stored procedures in information_schema
$StoredProceduresQry = @(Invoke-SQLCmd -query ("SELECT ROUTINE_NAME as Name from " + $Database.ToString() + ".INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_NAME NOT LIKE 'sp_%' order by 1") -Server $SqlServer)
# Store query result in array
$StoredProcedures = @($StoredProceduresQry | select-object -ExpandProperty Name)
# Declare array to store result in
$PackageSPArray = @("Solution;Project;Package;StoredProcedure")
# Get all SSIS packages in the configured folder and in all its subfolders
Get-ChildItem $PackagePath -Filter “*.dtsx” -Recurse | Where-Object { $_.Attributes -ne “Directory”} |
ForEach-Object {
# Ignore packages in the object folder
If (!$_.FullName.Contains("\obj\"))
{
# Read entire package content like it's a textfile
$PackageXml = Get-Content $_.FullName
# Loop through Stored Procedure array
ForEach ($StoredProcedure in $StoredProcedures)
{
# Optionally write search to screen
# Write-Host ("Looking for " + $StoredProcedure + " in package " + $_.Name)
# Check if it contains the stored procedure
If ($PackageXml | Select-String -Pattern $StoredProcedure)
{
# Optionally write find to screen
# Write-Host ("Found " + $StoredProcedure.ToString() + " in package " + $_.Name.ToString())
# Filling array: Solution;Project;Package;StoredProcedure
$PackageSPArray += $_.Directory.Parent.Name + ";"+ $_.Directory.Name + ";" + $_.Name + ";" + $StoredProcedure
}
}
}
}
# Optionally write result to screen
# $PackageSPArray | ForEach-Object {$_}
# Write result to file
$PackageSPArray | % {$_} | Out-File $ReportPath
There are a few considerations when using this script:
- It's looking for pieces characters! If your stored procedure has a very general name it might find that text where it isn't used as a stored procedure.
- It isn't honoring connection managers. If the same stored procedure is used in another database it will find it.
- It isn't honoring schemas. If you have a stored procedure multiple times in your database, but with different schemas it can't tell the difference.
- If your have a lot of stored procedures in your database and you have a lot of packages it could take a while, but it will always be faster then checking each package manually
Conclusion: the script isn't very intelligent, but very useful and fast for this case. All problems above can be solved, but then the script will be much bigger and take a lot of time to develop.