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.

Developing Microsoft SSIS packages programmatically with EZAPI

I recently started working on a task that required me to dig into creating SQL Server Integration Services packages programmatically.  There is not a lot of info out there on this, so thought it might be beneficial for me to blog a bit as I go in case other benefit from this.

Relatively early on in my investigation I found that the internal SSIS test team at Microsoft has created and released a second level API for SSIS called EzAPI that sits on top of the native SSIS API.  Using EzAPI, you can create packages and package objects in only a couple lines of simple code, vs. trying to decipher the somewhat esoteric COM wrapper APIs for SSIS.

You can read about EzAPI on the SSIS Team Blog here.

An update was released in late 2010, which you can read about here.  This update added a lot of additional components to the API.

You can download it here (including source code and samples).

The objects that are supported are as follows:

Tasks and Containers

• For Loop container
• DataFlow Task
• Execute Package Task
• ActiveX Script Task

DataFlow Components

• OLEDB Source
• OLEDB Destination
• FlatFile Source
• FlatFile Destination
• ADO.Net Source
• ADO.Net Destination
• Multicast Transform
• Derived Column Transform
• Sort Transform
• OLEDB command Transform
• Lookup Transform
• Cache Transform
• Data Convert Transform
• Aggregate Transform

Connection Managers

• OLEDB Connection manager (including specific versions for SQL Server, Oracle, DB2)
• FILE connection manager
• FLATFILE connection manager
• CACHE connection manager
• ADO.NET connection manager

ADDED IN DECEMBER 2010 UPDATE

• Sequence container
• For each loop container
• Transfer database task
• Execute process task
• File system task
• Execute SQL task
• Excel source adapter
• Excel destination adapter
• Union all transform
• Merge & Merge join transform
• Conditional split transform
• SCD transform

Index of all SQL Server build numbers since 7.0 Beta

Stumbled across a nice SQL Server build number site a while back that has a comprehensive (I think?) list of every SQL Server build from the SQL 7 beta 3 up through SQL 2012 RC1 including every CU and hotfix along the way.  There is also a link to download the KB that corresponds to each build if appropriate.  If you happen to know of a build that isn’t listed, it looks like this site is somewhat crowd-sourced, so post the details in the comments and it will likely make it up to the main page.

(If you look in the comments, you can even find little “anonymous” nuggets, such as the following: “SQL2012 RTM is 11.0.2100.19”)

SQL Server 2008 RC0 is available for public download

Surprisingly, I haven’t heard much buzz around this, but SQL Server 2008 RC0 is now available for public download at this link.  I haven’t had a chance to look at it yet, but supposedly this is the final release before RTM later this year.