Editing Existing SSIS Package via EzAPI or Standard SSIS API Doesn’t Update Layout in BIDS

As part of a project I am working on that requires me to programmatically alter existing packages, I ran into an annoyance (bug?) in SSIS that makes it hard to open packages after altering them.

The Problem

During an exchange on Codeplex forums with Matt Masson from the SSIS team, he summarized the workflow as follows:

1. Created the package in Visual Studio
2. Added stuff to it
3. Saved it out to disk
4. Opened and modified it with EzAPI

When I follow this workflow to add 10 connection managers, sequence containers and execute package tasks programmatically to an existing package with nothing in the design surface, you will end up with a package that will show connection managers, but no sequence containers or execute package tasks.

As you can see in the screenshot to the right, there are a lot of connection managers visible, but no accompanying sequence container or execute package task.

If you look at the Package Explorer tab, you will see that all of the “missing” sequence containers and execute package tasks were actually created and do exist in the package. You can see this in the Package Explorer in the screenshot to the left.  Additionally, if you execute the package from BIDS, programmatically added components appear in the output, just no green boxes or row counts in the Control Flow or Data Flow.

 

 

 

The Solution

Based on Matt Masson’s response to my question posted in the discussion area for EzAPI on Codeplex, I started digging into the XML layout code that is included at the end of the .dtsx package.  I found a blog post from the SQL Server Forum Support team that actually identified the problem I described above, and a solution.  Essentially the problem is that when a package is created in BIDS, the layout is created and stored inside a tag called <DTS:PackageVariable>.  When you modify a package programatically, this code is not updated since it is “BIDS generated code”.  When you open a package in BIDS after it has been altered, BIDS sees the existing layout code and doesn’t bother to parse the rest of the package to check for any missing objects for which layout code has not yet been created.

This problem does not occur on packages that are created programmatically from scratch because BIDS is able to detect that no layout code exists and will generate all of it the first time the package is opened and perform an auto-layout.

I adapted the code found at the SQL Server Forum Support Team FAQ to EzAPI with no additional problems.  One thing that I found is that there can be multiple <DTS:PackageVariable> sections, and IDS will not generate layout code until all of them have been removed.  To that end, my code uses Regex to get a count of how many code blocks will need to be purged.  Here is a sample of my adapted code (original is at the blog post linked above):

//Save the package object to XML string strXML = null; strXML = TestPackage.SaveToXML(); //Count instances of existing SSIS layout code in package. int LayoutCount = Regex.Matches(strXML, "<DTS:PackageVariable>").Count; //Remove the layout information. for (int i = 0; i < LayoutCount; i++) { strXML = strXML.Remove(strXML.IndexOf("<DTS:PackageVariable>"), strXML.IndexOf("</DTS:PackageVariable>") - strXML.IndexOf("<DTS:PackageVariable>") + 22); }

Based on comments that Matt made, I am hopeful that this has been fixed in SQL 2012, but the problem does exist in 2005, 2008 and 2008 R2.  If I discover any further useful information, I will update this blog post, but at a high level I think that I have covered the problem and solution.