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

A Better Starting Point for .Net Console Applications

Often when prototyping .Net applications, I end up starting with a console app (as things get more complicated or require interactivity, I move to Windows forms).  It takes about 1 minute to get up and running with a console app. . . unless you want to start passing fancy parameters and things like that.  It doesn’t take much more effort, but the point is that you don’t want to re-invent the console app code, you want to spend your time prototyping.

Sometimes your production application might require a console app.  In my case, one portion of the application I am working on will need to be implemented as a console app (for now) so that it can be executed from a SQL Server Agent job.  I want the console app to be robust and production-ready, very different from the prototype apps I cobble together in the span of a couple of minutes.

In support of a more robust console application, I found a blog post with a great template put together by Alois Kraus.  There is a blog post covering the basics, and the template can be downloaded from the Visual Studio Gallery.

At a high level, the custom console template starts you off in a Cadillac instead of a bicycle when building a console application.  Right out of the gate you get a nice command line parser, help output, color console and much more robust error handling.  I doubt I will start with the basic console template again.

CSVed – The World’s Best (free) CSV Editor/Viewer

If you work with data, you won’t be able to avoid delimited files for very long.  Sometimes you have to open these files.  If you don’t have Excel installed, you might end up having to use trusty old Notepad, which is not ideal from either a formatting or large file size perspective.  Excel is better, but no matter how you try to gloss it over, Excel is not designed as a delimited file viewer.

I have found that the right tool for the job in this case is a program that I have been using for years now called CSVed.  The newest version updates the UI, but there are tons of tools packed into this program and it can handle almost any file I have ever thrown at it, including 1GB+ files and delimited files that have bad data.  There is a unicode version of the program available as well should you need it.

Here is a link to the development homepage, you can download the latest version from there.

Complete SQL 2012 Demo Build Script

There is no need to download the 28GB BI Image X (which at the time of this writing hasn’t been updated to SQL 2012 RTM).  There are instructions on how to build an all-up SQL 2012 and SharePoint 2010 demo VM with instructions for configuring all of the latest SQL 2012 tools including DQS, MDM, PowerPivot for SharePoint and more.  The full instructions are here.  This is a great way to familiarize yourself with a full deployment of the latest BI software from Microsoft.

Here’s the summary from the link above:

This how-to article will guide you through the process of building a Hyper-V virtual machine with Microsoft SQL Server 2012, the latest Visual Studio 2010 development tools, SQL Server Data Tools (SSDT), and an assortment of other software products to create a test environment ready for testing and product demos.

Fix for Excessive VMWare Disk Activity On Host After Suspend or Shutdown

I use VMware to run 64-bit guest OS’s for development and testing on my laptop.  (Windows 7 only allows 32-bit guests, Windows 8 incorporates Hyper-V so this limitation will go away soon).  One of the nice benefits of running a VM is that I can suspend and bring it back online in a matter of seconds each time I want to use the VM.  Unfortunately, this comes with a negative side effect – the drive on the host that I am storing the VMs on thrashes for up to 15 minutes after VMWare was closed.  At the end of the day, if I had just suspended my VM and tried to shut down the Windows 7 host OS, it would take 15 minutes to shut down the laptop.

Fortunately I found the fix on a SharePoint blog.  Essentially you have add a setting to the VMWare config file.  For VMWare 7, the file is located here: C:\ProgramData\VMware\VMware Workstation\config.ini.  The setting that you need to add is mainMem.useNamedFile = “false”.  One thing to be careful of is if you copy/paste from the web, the quotes on the “false” will be curly directional style rather than the straight kind that are required for the config file.

One other setting that I changed was in the main preferences for VMWare under the Memory tab, I set the “How should the system allocate memory for virtual machines?’ to “Fit all virtual machine memory into reserved host RAM”.  I am able to do this since I have 16GB of RAM on my laptop, not sure if this is quite as feasible with less RAM.

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”)

Cross posting company blogs

I have been posting mostly to my company’s blog site recently, but for some reason those posts are not appearing in search results so I am going to post some of those entries here in case anyone else will benefit from them.