Get packages from SSIS Catalog

Case

I'm at a client and they lost the original SSIS Visual Studio projects. I have the packages deployed in the SSIS catalog. Is there a way to retrieve the packages from the catalog and create SSIS project from them?

Integration Services Catalog

Solution

First you should tell your client that they need to start using TFS (or similar versioning software)! And then you could use one of these three methods to recreate the SSIS projects. The catalog contains the packages, connection managers and parameters, but not the original Visual Studio (SSDT) projects, but recreating is quite easy.

A. Integration Service Import Project Wizard
B. Export in SSMS
C. PowerShell to the rescue

A. Integration Service Import Project Wizard

If your development PC, with SSDT/Visual Studio installed, can connect to the SQL Server with the catalog on it then this method is the easiest. Open SSDT, because it has an SSIS project with an import wizard!

1) Create a new Integration Services project

Instead of using the standard "Integration Services" - project, we need to use the "Integration Services Import Project Wizard" - project. For the project name you should use the name of the project in the SSIS catalog and for the solution name you could use the name of project folder from the SSIS catalog.

Integration Services Import Project Wizard

2) Wizard

Now the wizard will appear which allows you to select an SSIS Catalog project and export its content to your new SSIS project in Visual Studio. With just a few clicks your project is ready to use.

Animated GIF of wizard

B. Export in SSMS
If you cannot reach the server from within Visual Studio then there is an other option. Start SQL Server Management Studio and go to the SSIS Catalog. It has an option the export projects to an ISPAC file.

1) Right click your project and choose Export

When you export the project, you need to save the ISPAC file to a diskdrive or network folder. Use the project name from the catalog as the filename. 

Add caption

1b) Integration Service Import Project Wizard

If you saved the ISPAC file to a folder which can be reached from your development PC you could either go to method A and use the wizard to import the ISPAC file to your project or continue to step 2 and use an alternative method to recreate an SSIS project in SSDT.

2) Extract ISPAC file

If you saved the file as XXXX.ispac then you first need to rename the .ISPAC file to .ZIP, after that you can to extract all files from that zipfile.

Unzip the ISPAC file

3) Create new SSIS project

Because the solution and project files aren't saved within the Catalog you need to create a new/empty project in SSDT. For the projectname you should use the name of the project in the SSIS catalog and for the solution name you could use the name of project folder from the SSIS catalog.

Create new SSIS project in SSDT/Visual Studio

4) Add items from unzipped ISPAC

First remove the default package.dtsx file and then right click the project and choose Add, Existing Item... Then browse to your unzipped ISPAC file and select all files, except "@Project.manifest" and "[Content_Types].xml" (if you forget that the will end up in the Miscellaneous folder from your project).

If you import the Project.params file it will notice that there is already one. Replace it and after that reload the file.

After that you have your SSIS project back and it's probably time to put it under Source Control. Don't forget to change/check the package protection level of your packages and project.

Add project/solution to source control

C. PowerShell

If you have a lot ispac files to download then you could also use PowerShell. The only thing this solution doesn't do is creating a project in Visual Studio. You could either use method A or B for that or you could even try to create the XML for the xxxx.dtproj file with PowerShell. (download example)

 
#PowerShell: DownloadIspac.ps1
################################
########## PARAMETERS ##########
################################ 
# Change Server, folder, project and download folder
$SsisServer = "MyServer\SQL2014" # Mandatory
$FolderName = "MyFolder"         # Can be empty to download multiple projects
$ProjectName = "MyProject"       # Can be empty to download multiple projects
$DownloadFolder = "D:\MyIspacs\" # Mandatory
$CreateSubfolders = $true        # Mandatory
$UnzipIspac = $false             # Mandatory


#################################################
########## DO NOT EDIT BELOW THIS LINE ##########
#################################################
clear
Write-Host "========================================================================================================================================================"
Write-Host "== Used parameters =="
Write-Host "========================================================================================================================================================"
Write-Host "SSIS Server             :" $SsisServer
Write-Host "Folder Name             :" $FolderName
Write-Host "Project Name            :" $ProjectName
Write-Host "Local Download Folder   :" $DownloadFolder
Write-Host "Create Subfolders       :" $CreateSubfolders
Write-Host "Unzip ISPAC (> .NET4.5) :" $UnzipIspac
Write-Host "========================================================================================================================================================"


##########################################
########## Mandatory parameters ##########
##########################################
if ($SsisServer -eq "")
{
    Throw [System.Exception] "SsisServer parameter is mandatory"
}
if ($DownloadFolder -eq "")
{
    Throw [System.Exception] "DownloadFolder parameter is mandatory"
}
elseif (-not $DownloadFolder.EndsWith("\"))
{
    # Make sure the download path ends with an slash
    # so we can concatenate an subfolder and filename
    $DownloadFolder = $DownloadFolder = "\"
}


############################
########## SERVER ##########
############################
# Load the Integration Services Assembly
Write-Host "Connecting to server $SsisServer "
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
[System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;

# Create a connection to the server
$SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

# Create the Integration Services object
$IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

# Check if connection succeeded
if (-not $IntegrationServices)
{
    Throw [System.Exception] "Failed to connect to server $SsisServer "
}
else
{
    Write-Host "Connected to server" $SsisServer
}


#############################
########## CATALOG ##########
#############################
# Create object for SSISDB Catalog
$Catalog = $IntegrationServices.Catalogs["SSISDB"]

# Check if the SSISDB Catalog exists
if (-not $Catalog)
{
    # Catalog doesn't exists. Different name used?
    Throw [System.Exception] "SSISDB catalog doesn't exist."
}
else
{
    Write-Host "Catalog SSISDB found"
}


############################
########## FOLDER ##########
############################
if ($FolderName -ne "")
{
    # Create object to the folder
    $Folder = $Catalog.Folders[$FolderName]
    # Check if folder exists
    if (-not $Folder)
    {
        # Folder doesn't exists, so throw error.
        Write-Host "Folder" $FolderName "not found"
        Throw [System.Exception] "Aborting, folder not found"
    }
    else
    {
        Write-Host "Folder" $FolderName "found"
    }
}


#############################
########## Project ##########
#############################
if ($ProjectName -ne "" -and $FolderName -ne "")
{
    $Project = $Folder.Projects[$ProjectName]
    # Check if project already exists
    if (-not $Project)
    {
        # Project doesn't exists, so throw error.
        Write-Host "Project" $ProjectName "not found"
        Throw [System.Exception] "Aborting, project not found"
    }
    else
    {
        Write-Host "Project" $ProjectName "found"
    }
}


##############################
########## DOWNLOAD ##########
##############################
Function DownloadIspac
{
    Param($DownloadFolder, $Project, $CreateSubfolders, $UnzipIspac)
    if ($CreateSubfolders)
    {
        $DownloadFolder = ($DownloadFolder + $Project.Parent.Name)
    }

    # Create download folder if it doesn't exist
    New-Item -ItemType Directory -Path $DownloadFolder -Force > $null

    # Check if new ispac already exists
    if (Test-Path ($DownloadFolder + $Project.Name + ".ispac"))
    {
        Write-Host ("Downloading [" + $Project.Name + ".ispac" + "] to " + $DownloadFolder + " (Warning: replacing existing file)")
    }
    else
    {
        Write-Host ("Downloading [" + $Project.Name + ".ispac" + "] to " + $DownloadFolder)
    }

    # Download ispac
    $ISPAC = $Project.GetProjectBytes()
    [System.IO.File]::WriteAllBytes(($DownloadFolder + "\" + $Project.Name + ".ispac"),$ISPAC)
    if ($UnzipIspac)
    {
        # Add reference to compression namespace
        Add-Type -assembly "system.io.compression.filesystem"

        # Extract ispac file to temporary location (.NET Framework 4.5) 
        Write-Host ("Unzipping [" + $Project.Name + ".ispac" + "]")

        # Delete unzip folder if it already exists
        if (Test-Path ($DownloadFolder + "\" + $Project.Name))
        {
            [System.IO.Directory]::Delete(($DownloadFolder + "\" + $Project.Name), $true)
        }

        # Unzip ispac
        [io.compression.zipfile]::ExtractToDirectory(($DownloadFolder + "\" + $Project.Name + ".ispac"), ($DownloadFolder + "\" + $Project.Name))

        # Delete ispac
        Write-Host ("Deleting [" + $Project.Name + ".ispac" + "]")
        [System.IO.File]::Delete(($DownloadFolder + "\" + $Project.Name + ".ispac"))
    }
    Write-Host ""
}


#############################
########## LOOPING ##########
#############################
# Counter for logging purposes
$ProjectCount = 0

# Finding projects to download
if ($FolderName -ne "" -and $ProjectName -ne "")
{
    # We have folder and project
    $ProjectCount++
    DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac
}
elseif ($FolderName -ne "" -and $ProjectName -eq "")
{
    # We have folder, but no project => loop projects
    foreach ($Project in $Folder.Projects)
    {
        $ProjectCount++
        DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac
    }
}
elseif ($FolderName -eq "" -and $ProjectName -ne "")
{
    # We only have a projectname, so search
    # in all folders
    foreach ($Folder in $Catalog.Folders)
    {
        foreach ($Project in $Folder.Projects)
        {
            if ($Project.Name -eq $ProjectName)
            {
                Write-Host "Project" $ProjectName "found in" $Folder.Name
                $ProjectCount++
                DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac
            }
        }
    }
}
else
{
    # Download all projects in all folders
    foreach ($Folder in $Catalog.Folders)
    {
        foreach ($Project in $Folder.Projects)
        {
            $ProjectCount++
            DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac
        }
    }
}

###########################
########## READY ##########
###########################
# Kill connection to SSIS
$IntegrationServices = $null
Write-Host "Finished, total downloads" $ProjectCount

Output example