Extract all the connection strings from SSIS packages

One important task in SSIS upgrade/migration project is the assessment of data provider types, source and destination connection strings in the all packages. The output is used to understand which data source and destination are involved, which data providers are in used. You could use SSDT to check SSIS package but you need a quicker way if the number of package is huge. In my recent project, the number of SSIS package is larger than 600, and below is PowerShell method I use to retrieve the information in 5 min.

Step 1: Export SSIS packages from msdb

The first step is to store all SSIS packages in one main folder. I use the PowerShell from Jamie Thomson to export SSIS packages from msdb. Here is the link: Export all SSIS packages from msdb using Powershell

Step 2: Retrieve the data providers and strings

Once the packages are stored in the folder, said "C:\ssis_data_provider_check", you could use below PowerShell to dig out information of data providers and connection strings.

[ps]
############################################################
## Retrieve the data providers and strings
##
## Version 0.1
## Created Date 3 Aug, 2016
## Created By Shiyang Qiu (shiyang.qiu@microsoft.com)
############################################################

cls
# $path is the folder of SSIS pacakage
$path = "C:\ssis_data_provider_check"
Push-Location $path

#$all_type_file is the txt file to store the type of data provider
$all_type_file = "$path\all_provider.txt"

#If the file exists, remove it
if(Test-Path $all_type_file) {Remove-Item $all_type_file -force}

#Findout the all provider types, each data type will be one line in the $all_type_file
$Lines = dir -I *.dtsx -R | select-string -Pattern "Provider="
foreach ($L in $Lines)
{
#RegExp is used to extract the name of data provider
$L.Line -match '(Provider=)\w+' | Out-Null; $Matches[0] >> $all_type_file
}

#Find out all the data connection strings and store into text file for each data provider.
$all_type_array = get-content .\all_provider.txt | sort| Get-Unique

foreach($type in $all_type_array)
{
dir -I *.dtsx -R | select-string -Pattern $type | Format-Table -auto -Property path, lineNumber, Line | out-string -Width 4096 | out-file $path\$type.txt
}

[/ps]
Here is the result sample:

-- Posted by Shiyang, Aug 3, 2016