Downloading all packages from your SQL Server

Case
A few month ago I had a job to add some adjustments to a SSIS project, but they lost the Visual Studio project file. So I created a empty project and started adding all the packages (over a 150) from SQL Server to the project one by one. After five packages I realised this could take ages. A while ago I created a package to upload all Visual Studio project files to SQL Server and thought I can do that vica versa!

Solution
I created a package with a single Script Task and used the SSIS API to download all packages to the Visual Studio SSIS project folder. After that I edited the Visual Studio SSIS project file (.dtproj, an XML file) to add the packages to the project itself.

// C# code
// Script to download packages from a SQL Server and add them
// to a Visual Studio SSIS Project file. The script assumes
// that the package name and package object name are equal.
using System;
using System.Data;
using System.Xml; // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_fe8263e907c94504a4c41974a46fc623.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            /////////////////////////////////////////
            // The Project file and folder
            String ProjectFilePath = @"d:\Integration Services Project\Integration Services Project.dtproj";
            String ProjectFolderPath = ProjectFilePath.Substring(0,ProjectFilePath.LastIndexOf(@"\")+1);

            // The SQL Server
            String SSISServer = "Name_Of_Your_Server";

            /////////////////////////////////////////
            // Variable that contains the Visual Studio SSIS Project file, an XML document     
            XmlDocument doc = new XmlDocument();
            doc.Load(ProjectFilePath);

            // Get DTSPackages to add a package
            XmlNode DTSPackages = doc.SelectSingleNode("/Project/DTSPackages");

            /////////////////////////////////////////
            // Set application context
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

            // Create a package variable to temporary store the server packages
            Package serverPackage;

            /////////////////////////////////////////
            // Loop through packages and folders in the root: / (or change the path for your folder)
            foreach (PackageInfo serverPackageInfo in app.GetPackageInfos("/", SSISServer, null, null))
            {
                // Get only the packages. You could make a recursive function to loop through all folders
                if (serverPackageInfo.Flags.ToString().ToLower().Equals("package"))
                {
                    /////////////////////////////////////////
                    // Fill the package variable with a server package
                    serverPackage = app.LoadFromSqlServer(serverPackageInfo.Folder + serverPackageInfo.Name, SSISServer, null, null, null);

                    // Save the package to xml (.dtsx) in the project folder.
                    app.SaveToXml(ProjectFolderPath + serverPackageInfo.Name + ".dtsx", serverPackage, null);

                    /////////////////////////////////////////
                    // Dirty/lazy check to see if the package already exists in the project file (can't add them twice)
                    bool fireAgain = true;
                    if (DTSPackages.InnerXml.Contains(serverPackage.Name + ".dtsx"))
                    {
                        Dts.Events.FireInformation(0, "Download package", "Existing local package had been overwritten by Server package", string.Empty, 0, ref fireAgain);
                    }
                    else
                    {
                        /////////////////////////////////////////
                        // Add package to project xml file
                        // <dtspackage formatversion="3"></dtspackage>
                        XmlNode newDtsPackageNode = doc.CreateNode(XmlNodeType.Element, "DtsPackage", "");
                        XmlAttribute newDtsPackageAttribute = doc.CreateAttribute("FormatVersion");
                        newDtsPackageAttribute.Value = "3";
                        newDtsPackageNode.Attributes.Append(newDtsPackageAttribute);

                        // <name>Datastaging.dtsx</name>
                        XmlNode newDtsPackageNameNode = doc.CreateNode(XmlNodeType.Element, "Name", "");
                        newDtsPackageNameNode.InnerText = serverPackage.Name + ".dtsx";

                        // <fullpath>Datastaging.dtsx</fullpath>
                        XmlNode newDtsPackageFullPathNode = doc.CreateNode(XmlNodeType.Element, "FullPath", "");
                        newDtsPackageFullPathNode.InnerText = serverPackage.Name + ".dtsx";

                        // <references />
                        XmlNode newDtsPackageReferencesNode = doc.CreateNode(XmlNodeType.Element, "References", "");

                        // Add child nodes to <dtspackage>
                        newDtsPackageNode.AppendChild(newDtsPackageNameNode);
                        newDtsPackageNode.AppendChild(newDtsPackageFullPathNode);
                        newDtsPackageNode.AppendChild(newDtsPackageReferencesNode);

                        // Add <dtspackage> to <dtspackages>
                        DTSPackages.AppendChild(newDtsPackageNode);
                        Dts.Events.FireInformation(0, "Download package", "Package from server has been added to the project.", string.Empty, 0, ref fireAgain);
                    }
                }
            }
            /////////////////////////////////////////
            // Save project
            doc.Save(ProjectFilePath);
           
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

I hard coded the project file and server name, but you can change that by using variables and configure those.