SQLDBA : export or download SSIS packages

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:

1. Review TFS, Bugzilla, and other repositories to confirm the availability of the latest package source files.
2. Download all deployed .dtsx files from the Integration Services Server.
3. Preserve the existing deployment folder hierarchy while exporting the packages.
4. 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."