EzAPI - When package loaded from disk and then saved to file the BIDS Control Flow surface is not initialized

Jan 25, 2012 at 8:28 PM

I just posted this to Integration Services forums, so not trying to spam with multiple posts, but not sure where I can post that is most visible:

I have recently discovered EzAPI and although I am still getting familiar with it, I love it.  One problem that I have run into and not yet solved is this:

  1. Load an existing package using the LoadFromFile method of the EZPackage object.
  2. Add n number of components to the package object using EzAPI
  3. Save the package to disk using the SaveToFile method of EzPackage object

The problem that I am running into is that when I open the package in BIDS I do not see the objects on the Control Flow.  However, I can see that they are there as expected in the Package Explorer.  When I view the XML behind the .dtsx file, it appears that the code that defines the visual layout interpreted in BIDS is missing for the new objects.

If I use EzAPI to create a package from scratch, the visual XML code is there and works fine.  Any ideas or insights are appreicated.  It is worth noting that the packages execute fine and can be opened in BIDS.  You just don't see anything visually that was added via EzAPI.

Thanks,

Josh

Coordinator
Jan 27, 2012 at 2:56 PM

Hi Josh,

It sounds like an issue with the layout info. I'm guessing you did this:

  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 an SSIS package is opened in the designer, it uses the embedded layout information to display the boxes. If there is no layout information (which is the case when the package is completely generated programmatically), SSIS will do an auto-layout for everything. Unfortunately, it didn't check for missing layout information in 2005/2008, but I think we fixed this in 2012.

You have a couple of ways to resolve this.

  1. Remove the existing layout from the package after modifying it with EzAPI (not sure how easy that is through the API - you might need to extract it from the XML directly)
  2. Do an auto-layout after opening the package in the SSIS designer. I believe this will replace everything, and allow the new tasks to show up (I haven't tried this out, but I think it might work)

 

Feb 1, 2012 at 7:29 PM
Edited Feb 1, 2012 at 7:31 PM

Matt - thanks for the response.  You nailed it, and based on the info you gave, I have been able to solve this programmatically.  Option 2 that you put out there doesn't work, but was worth a shot.

I created a blog post with the problem/solution, so hopefully the next person will find this posting and that blog post.  Not sure if I am allowed to post links, but here it is: http://key2consulting.com/Blogs/jrobinson/2012/02/01/editing-existing-ssis-package-via-ezapi-or-standard-ssis-api-doesnt-update-layout-in-bids/

Also, once I was on the right track, I found a post from the SQL Server Forum Support Team that describes the exact problem and provides the XML search/replace code to solve it.  Here is a link to that posting for anyone else that needs it in the future: http://blogs.msdn.com/b/sqlforum/archive/2011/02/28/faq-how-do-i-refresh-the-layout-information-of-a-sql-server-integration-services-ssis-package-that-is-modified-programmatically.aspx

Here is the code that I came up with to fix it, you could just use this code in any "SavePackage" methods that you write:

//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);
}

 

Thanks again for the help!

-Josh