I am moving a number of SSIS configuration files from one folder structure to another as part of a SQL Server upgrade. Both folder structures have each of the files but they point to different file drop locations, SQL Instances, etc. My task was to compare the configuration settings in each environment and map all the changes from the old environment to the new.
Rather than opening each file and visually checking for changes I went with a PowerShell approach centered on the Compare-Object cmdlet. This cmdlet compares two objects and produces a comparison report. This will work whether the two objects are folder structures, text files, or other objects.
My approach was to start with two folders containing .dtsconfig files. First compare the two sets of files and return a list of files that are in both folders. Then, file pair by file pair, open each file and compare contents. Write any differences to an output file. See the comments below for more details.
$Path1 = “C:\Path1”
$Path2 = “C:\Path2”
$OutFile = “C:\ComparisonOutputFile.txt”
# Delete outfile if it exists
# Write the two paths to the outfile so you know what you’re looking at
# Compare two folders and return only files that are in each
# Loop the file list and compare file contents
As you can see, PowerShell provides significant functionality with very little effort. I was particularly impressed with the comparison report. The SideIndicator column (see below) is a simple way to communicate the differences. This is a typical output file (the names were changed to protect the innocent).
While it isn’t a fancy GUI it is functional, visual, extensible, and built in. The extensible is important because you don’t get that from a GUI. Suppose I wanted to keep a “Gold Copy” of my config files and run a daily comparison against production to pick up changes? The code above is almost custom written for that process – all it needs is an email step. Further, you could use a similar process using SMO to generate database schema and do you own comparisons. The possibilities are truly endless.