There may come a day where you need to verify you have access to connect to a SQL Server instance. Perhaps you need to verify this access for a hundred or maybe even a thousand servers. Doing this by hand is not great and you’re going to want to capture what you did and did not have access to. This PowerShell script will help you accomplish that.
Continue reading “Verify access to many SQL Server instances”Tag: Powershell
Where did I put that thing in my SSIS project?
You may inherit a large SSIS project or create / use a large SSIS project someday, or perhaps there’s just a lot of packages. Either way, there’s going to come a day and time where you have to find something in your SSIS packages.
Say you need to remove a column in a table but you’re not sure where that column is referenced in your SSIS packages? Perhaps you are having metadata issues and you need to find every reference to that column? Or maybe you want to see what packages reference what tables, etc.
There are numerous ways to slice and dice this, I created essentially a powershell grep function to cursor through my dtsx packages and find strings referenced.
Here it is!
# Variables you should alter to your environment
$DirectoryToTarget="C:\Git\SSIS\PackageSets1\"
$WordToFind="TableSourceOne"
$PSGrepLog="C:\LogOutputs\PSGrepLog.txt"
# You will probably leave this variable alone. Set to search SSIS.
$FilterType="*.dtsx"
Clear-Content $PSGrepLog
Get-ChildItem -Path $DirectoryToTarget -Filter $FilterType -Recurse | where { !$_.PSIsContainer } | % {
$file = Get-Content $_.FullName
$containsWord = $file | %{$_ -match $WordToFind}
If($containsWord -contains $true)
{
Add-Content $PSGrepLog $_.FullName
}
}
Few notes here. It is not perfect, it is not case sensitive, and it will cursor child directories. It can take a few minutes to run depending on your system and the size of the directory. I would not recommend running this on a production server. You should copy the files you need to a nice location away from production and run this.
Once you have a list of packages with references, you can edit each package with a notepad editor or visual studio will let you examine the XML form. From here you can do a CTRL+F function on your keyboard and type in your searching keyword. It will take you to each instance / reference in your package, making it substantially quicker to find those rogue references.
Happy developing!
Edit on 5/4/18.
I have found out that visual studios actually offers the ability to perform the same functionality at a project level. I think my code is still helpful for particular situations, but you should be able to use the menu and find feature to search through all other packages too for your reference. I’m definitely saving my code as it should work across file types with a few minor tweaks.