Partilhar via

FAQ: How do I refresh the layout information of a SQL Server Integration Services (SSIS) package that is modified programmatically?

  • Artigo

Question

 You design a SQL Server Integration Services (SSIS) package using Business Intelligence Development Studio (BIDS) and save the package. Then you modify the package programmatically, save the package, and then re-open the package in BIDS. Now, you notice the layout information is not updated correctly. For example, you add a Sequence container programmatically to a pre-defined Foreach Loop container. In BIDS, the Sequence container is not visible. Or, you add a task to a container. When you move the container in BIDS, the task is not moved together with the container.

 

Answer

 The problem is due to the fact that when the package was first made in Business Intelligence Development Studio (BIDS), the layout information was saved within a xml tag called <packagevariable>, and all elements have their layouts written in encoded xml in the xml file.

When you modify the package through .NET and add new elements, the layout information is not updated. So when the package is loaded within the BIDS for modification, the BIDS recognizes that some layout information is available, so it will use this information to layout the ENTIRE PACKAGE. This cause the problem.

 

To solve the issue, you can remove the layout information programmatically, then the package will layout the package in a default manner. The code below is for your reference:

 

      Application app = new Application();

      string path = @"D:\Libraries\Documents\Visual Studio 2008\Projects\WCFSSIS\WCFSSIS\FEL.dtsx";

      Microsoft.SqlServer.Dts.Runtime.Package pkg = app.LoadPackage(path, null);

      Executable exec = pkg.Executables["FLC"];

      (exec as IDTSSequence).Executables.Add("STOCK:SEQUENCE");

      string strXML = null;

      pkg.SaveToXML(out strXML, null);

      //Remove the layout information.

      strXML = strXML.Remove(strXML.IndexOf("<DTS:PackageVariable>"), strXML.IndexOf("</DTS:PackageVariable>") - strXML.IndexOf("<DTS:PackageVariable>") + 22);

      strXML = strXML.Remove(strXML.IndexOf("<DTS:PackageVariable>"), strXML.IndexOf("</DTS:PackageVariable>") - strXML.IndexOf("<DTS:PackageVariable>") + 22);

      Package pkg2 = new Package();

      pkg2.LoadFromXML(strXML, null);

      app.SaveToXml(@"D:\package.dtsx", pkg2, null);

 

 

Applies to

 SQL Server Integration Services 2005

SQL Server Integration Services 2008

SQL Server Integration Services 2008 R2

Comments

  • Anonymous
    February 03, 2012
    This is exactly what I needed.  Thank you!

  • Anonymous
    February 03, 2012
    Here is an alternate strategy, a bit slicker:        using System.Xml.Linq;        const string DTS_NAMESPACE = "www.microsoft.com/SqlServer/Dts";        private Package StripPackageFormatting(Package package)        {            string packageXML = null;            package.SaveToXML(out packageXML, null);            XNamespace dtsNamespace = DTS_NAMESPACE;            XElement xPackage = XElement.Parse(packageXML);            xPackage.Elements(dtsNamespace + "PackageVariable").Remove();            Package reformattedPackage = new Package();            reformattedPackage.LoadFromXML(xPackage.ToString(), null);            return reformattedPackage;        }