Friday 31 December 2010

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.

4 comments:

  1. I tried using this but the package path that I have contains a white space and this breaks the script. Any ideas?

    ex:
    instead of "/Project/DTSPackages"
    I have "/Project/SSIS Packages"

    thanks,

    ReplyDelete
    Replies
    1. Which version of SSIS are you using? The project file for each version of SSIS is different. You will have to adjust the script...

      Delete
  2. We use SSIS 2008R2. I know the path of the project file, the problem is that the filepath contains a white space and I don't know how to make an XPath expression allow that. I also can't change the project file path because we have offshore developers using it and I am under severe time constraints. Any ideas on how to workaround this?

    Thanks,

    ReplyDelete
    Replies
    1. I just opened a project file from 2008 R2 and it is showing:
      DTSPackages

      Contact me via the webform in the upperright menu, so that we can communicate via email...

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...