As part of the SSIS package upgrade planning, we need to ensure that the latest source code for all deployed packages is available in our source repositories such as TFS, Bugzilla, or any other code repository.
Before starting the upgrade activity, we should verify whether the deployed SSIS packages have corresponding source files available in the repository. If the latest versions are not available, there is a risk of losing the ability to recover or re-deploy packages in case of issues during the upgrade process.
To mitigate this risk, I propose the following:
- Review TFS, Bugzilla, and other repositories to confirm the availability of the latest package source files.
- Download all deployed .dtsx files from the Integration Services Server.
- Preserve the existing deployment folder hierarchy while exporting the packages.
- Store the downloaded packages in a secure backup location before initiating the upgrade.
This backup will serve as a fallback copy and help ensure a smooth rollback or recovery process if required.
-- Query will give List package names with deploymenet path.
USE msdb
GO
;WITH FolderHierarchy AS
(
SELECT
folderid,
parentfolderid,
foldername,
CAST(foldername AS VARCHAR(MAX)) AS FullPath
FROM msdb.dbo.sysssispackagefolders
WHERE parentfolderid IS NULL
UNION ALL
SELECT
f.folderid,
f.parentfolderid,
f.foldername,
CAST(h.FullPath + '\' + f.foldername AS VARCHAR(MAX))
FROM msdb.dbo.sysssispackagefolders f
JOIN FolderHierarchy h
ON f.parentfolderid = h.folderid
)
SELECT
h.FullPath AS FolderPath,
p.name AS PackageName,
h.FullPath + '\' + p.name AS PackagePath
FROM msdb.dbo.sysssispackages p
JOIN FolderHierarchy h
ON p.folderid = h.folderid
ORDER BY h.FullPath, p.name;
Below power shell script will be help to download al dtsx files.
-- here we need to pass integration server name
$SqlServer = "TFSSIS2017DB,51117"
$OutputFolder = "C:\SSISBackup"
if (!(Test-Path $OutputFolder))
{
New-Item -ItemType Directory -Path $OutputFolder -Force | Out-Null
}
$query = @"
;WITH FolderHierarchy AS
(
SELECT
folderid,
parentfolderid,
foldername,
CAST(foldername AS VARCHAR(MAX)) AS FolderPath
FROM msdb.dbo.sysssispackagefolders
WHERE parentfolderid IS NULL
UNION ALL
SELECT
f.folderid,
f.parentfolderid,
f.foldername,
CAST(h.FolderPath + '\' + f.foldername AS VARCHAR(MAX)) AS FolderPath
FROM msdb.dbo.sysssispackagefolders f
INNER JOIN FolderHierarchy h
ON f.parentfolderid = h.folderid
)
SELECT
p.name AS PackageName
, ISNULL(h.FolderPath,'Root') AS FolderPath
, CAST(CAST(p.packagedata AS VARBINARY(MAX)) AS XML) AS PackageXML
FROM msdb.dbo.sysssispackages p
LEFT JOIN FolderHierarchy h
ON p.folderid = h.folderid
ORDER BY FolderPath, PackageName
"@
$packages = Invoke-Sqlcmd `
-ServerInstance $SqlServer `
-Database msdb `
-Query $query
foreach ($pkg in $packages)
{
# Create local folder structure matching MSDB hierarchy
if ($pkg.FolderPath -eq "Root")
{
$LocalFolder = $OutputFolder
$PackagePath = "\" + $pkg.PackageName
}
else
{
$LocalFolder = Join-Path $OutputFolder $pkg.FolderPath
$PackagePath = "\" + $pkg.FolderPath + "\" + $pkg.PackageName
}
if (!(Test-Path $LocalFolder))
{
New-Item -ItemType Directory -Path $LocalFolder -Force | Out-Null
}
$FileName = Join-Path $LocalFolder ($pkg.PackageName + ".dtsx")
$pkg.PackageXML.OuterXml | Out-File `
-FilePath $FileName `
-Encoding utf8
Write-Host "Package Path : $PackagePath"
Write-Host "Exported To : $FileName"
Write-Host "--------------------------------------"
}
Write-Host "Export completed."
This second script: with little bit changes with above code.
$SqlServer = "TFSSIS2017DB,51117"
$OutputFolder = "C:\SSISBackup"
if (!(Test-Path $OutputFolder))
{
New-Item -ItemType Directory -Path $OutputFolder -Force | Out-Null
}
$query = @"
SELECT
p.name AS PackageName,
ISNULL(f.foldername,'Root') AS FolderName,
CAST(CAST(p.packagedata AS VARBINARY(MAX)) AS XML) AS PackageXML
FROM msdb.dbo.sysssispackages p
LEFT JOIN msdb.dbo.sysssispackagefolders f
ON p.folderid = f.folderid
ORDER BY FolderName, PackageName
"@
$packages = Invoke-Sqlcmd `
-ServerInstance $SqlServer `
-Database msdb `
-Query $query
foreach ($pkg in $packages)
{
# Build full package path
$PackagePath = if ($pkg.FolderName -eq 'Root')
{
"\" + $pkg.PackageName
}
else
{
"\" + $pkg.FolderName + "\" + $pkg.PackageName
}
# Create matching local folder structure
$FolderPath = Join-Path $OutputFolder $pkg.FolderName
if (!(Test-Path $FolderPath))
{
New-Item -ItemType Directory -Path $FolderPath -Force | Out-Null
}
$FileName = Join-Path $FolderPath ($pkg.PackageName + ".dtsx")
# Export package
$pkg.PackageXML.OuterXml | Set-Content `
-Path $FileName `
-Encoding UTF8
Write-Host "Exported Package Path: $PackagePath"
Write-Host "Local File : $FileName"
Write-Host ""
}
Write-Host "Export completed."
