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

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.

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

Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables

My buddy Wayne just brought to my attention a white paper that came out just a year ago from Microsoft that describes how to design SSAS 2005 cubes for use with Excel 2007.  I'm posting it here for my own future reference.  Here is the description:

Overview

Microsoft Office Excel 2007 takes advantage of most of the features in Microsoft SQL Server 2005 Analysis Services. To take full advantage of these features, it is important to keep in mind the end-user experience in Office Excel 2007 when you are designing cubes.
This document outlines how you can create a good end-user experience by optimizing the cube design for Office Excel 2007 PivotTable dynamic views.

Programmatically executing SSIS packages

For a good summary of many different ways to run an SSIS package programmatically, as well as advantages and drawbacks of each, look here at Michael Entin's blog.  There are some good things in the comments as well.  This will come in handy at some point, I am sure.

Upgrading Existing SSIS Packages to TableDifference Component 2.0

I am an avid user of Alberto Ferrari's SQL Server Integration Services "Table Diff" component, which you can download from http://www.sqlbi.eu.  He recently released the new, improved version 2.0 of the component (the .dll still says that it is 1.0 though?) and I am upgrading a mid-size solution that makes heavy use of the prior version of TableDiff (1.x) to use the new component.  Here are my observations of both the upgrade and the new capabilities of the component.

Here is a typical Type 1 style update package (in this case an import table, not a dimension) that uses TableDifference at its core to compare the new and old data flows and direct output accordingly.  Behind the scenes, I overwrote the TableDifference.dll file with the new one in both the .NET Assembly Cache and the \PipelineComponents directory.  When I opened the package, everything is working except the Update and Insert components have red X's.  These are easily fixed by simply double-clicking on each to open properties, accepting the new automatically matched column references and saving the package.  That's it, there is nothing else required to update your pacakages from TableDifference 1.x to the newly released 2.0.

Excel 2003 Add-in for Analysis Services

If your company (like most companies) is still using Excel 2003 and you are implementing a data warehouse which contains SSAS cubes, you might be interested in the Microsoft Excel 2003 Add-in for SQL Server Analysis Services.  I have not used this tool extensively, but it does bring Excel 2003 closer to Excel 2007 in terms of OLAP capabilities.  Here is the blurb from the download page, I will post more as I spend time with the tool.  This is not a new find, nor is it likely news to anybody, but worth noting so I am putting it here.

The Excel Add-in for Analysis Services enables users to access and analyze data from multiple Analysis Services Cubes, and to create rich, customized reports directly in Microsoft Office Excel 2003 or Microsoft Excel 2002. This download can improve data analysis, shorten reporting cycles, and enhance your company's ability to respond to customers.
This download not only reduces the time and resources required to train users, but it also eliminates the need for organization’s to support specialized reporting systems and tools. The key business benefits of Excel Add-in for Analysis Services include:

• Visibility in Excel into business trends
• Increased speed and quality of decision-making
• Streamlined data analysis that shortens reporting cycles and saves resources
• Increased reporting flexibility through rich, highly customized, and refreshable reports
• Access to relevant information from multiple data sources

About the Excel Add-in for Analysis Services
With the Excel Add-in for Analysis Services, individual users can manage the reporting cycle from beginning to end and eliminate the need to cut and paste data from multiple systems.

• Access: Easily create and maintain live data connections to multiple Analysis Services cubes, ensuring data consistency and integrity, and combine data from multiple sources into a single report.
• Analyze: Conduct detailed analysis using native Excel capabilities. Extend the richness of analysis through “what if” and drill through capabilities.
• Author: Easily personalize and refresh report layouts. Minimize end user training and reduce reliance on IT due to Excel’s popularity and ease of use.

The Data Warehouse Lifecycle Toolkit 2nd Edition is now available

The first one is a great reference, but was published in 1998.  Since then the art and science of building the data warehouse has matured significantly.  I don't think that the word BI really existed in 1998. . . Actually I don't know since I didn't start working in the DW/BI world until 1999 with Brio, then Business Objects.

The original had a lot of great material for organizing the pro cess around preparing for, implementing and maintaining the data warehouse, including a nice set of documents.  Here is a link to the new book on Amazon.

New set of SSIS Tasks

These may have been around for a while, but I just discovered that there is a nice set of (affordable) SSIS tasks at CozyRoc.  There is a task for zipping files, managing partitions, SFTP and even using another dataflow as a source.  Here is the current info from their site:

 

Component
Key Features

Beta! Package SSIS Connection Manager

• Gain access to the current or different package at runtime.
• Support for all current types of package storage: SQL, File, SSIS Package Store.
• Support for package variables initialization before execution.
• Easy to use interface.

Beta! Data Flow Source SSIS Data Flow Component

• Based on Package Connection Manager.
• Very fast, non-blocking, in-memory data read from another data flow.
• Scalable alternative of the solution Microsoft is recommending with raw data file source.
• User interface is based on the standard data flow source component dialog.

Beta! Data Flow Destination SSIS Data Flow Component

• Exposes data from a data flow for in-memory read in another data flow.
• Scalable alternative of the solution Microsoft is recommending with raw data file destination.
• User interface is based on the standard data flow destination component dialog.

Database Partitions SSIS Control Flow Task

• Saves you time by handling the intricacies of the partitioning and letting you concentrate on your workflow instead.
• Create, insert and remove database partitions in Microsoft SQL Server 2005.
• Combine smaller partitions into a larger one, for easy backup later.
• Support for ADO.NET, OLEDB, ADO connection managers.
• Easy to use interface.

Script Task Plus SSIS Control Flow Task

• Extension of Microsoft SSIS Script Task, meaning you already have the skills to use it.
• Greater reusability and easier maintenance of script functionality.
• Customizable script user interface and setup.
• SSIS Script export, import or linking to.
• Keep your script code separate from your package and have easier time tracking changes in your source control system.
• Requires SQL Server 2005 SP2.
• New! Share your exported SSIS scripts with the community.

Zip SSIS Control Flow Task

• Compression and decompression of Zip, GZip, BZip2, Unix, Tar archives.
• Automatically switches to Zip64 format, if it is required.
• Streamlined user interface.

SSH SSIS Connection Manager

• Secure connections with SSH-enabled server.
• Authenticate with password or private key file.
• Support for Tunnel (HTTP), SOCKS4, SOCKS5 proxy.
• Streamlined user interface.

SFTP SSIS Control Flow Task

• Based on SSH Connection Manager.
• Secure-FTP (SFTP) communications and management.
• Support for SFTP commands send, receive and delete files, create and remove directory, rename file.
• The user interface covers most of the functionality of the standard Microsoft SSIS FTP task and it is very similar.

SSH Execute SSIS Control Flow Task

• Based on SSH Connection Manager.
• Secure execution of shell commands on a remote SSH server.
• Streamlined user interface.

More Interesting SSIS Components

I knew about the Konesans Trash Destination, but it turns out that they have a whole series of free, publicly available SSIS components.  The main product page is here, and below I have pasted in content for each of the transforms, with the link to the download page:

Checksum Transformation

 The Checksum Transformation computes a hash value, the checksum, across one or more columns, returning the result in the Checksum output column. The transformation provides functionality similar to the T-SQL CHECKSUM function, but is encapsulated within SQL Server Integration Services, for use within the pipeline without code or a SQL Server connection. Checksums can reduce network contention and increase process performance by allowing you to verify data through a single value rather than transferring all data values for comparison.

Data Generator Source

This source component literally generates data. Specify how many columns you want, and how many rows, then watch the data flow out. Build demonstration and research scenarios faster with this simple source.

File Watcher Task

The File Watcher Task does what it says really, it watches a folder waiting for files. When an available file is found the task completes, returning the name of the file for onward use within the package.

Regular Expression Transformation

The Regular Expression Transformation exposes the power of regular expression matching within the pipeline. One or more columns can be selected, and for each column an individual expression can be applied. If all columns selected pass their tests then rows are passed down the successful match output. Rows that fail to pass all tests are directed down the alternate output.

Row Count Plus Transformation

The Row Count Plus Transformation can replace the stock transformation. We have recreated and extended with more functionality and a user friendly interface for faster and easier package design.

Row Number Transformation

The Row Number Transformation calculates a row number for each row. It offers ROW_NUMBER or IDENTITY like behaviour within the Data Flow. Uses include surrogate key generation or data partitioning within the pipeline.

Trash Destination

The Trash Destination Adapter is a development aid. It allows you to quickly terminate a data flow path, and does not require any configuration. It will consume the rows without any side effects, and prevents warnings or errors you may otherwise receive when executing the data flow.

Additional SSIS Transforms that didn't ship with the product

There are a whole series of SSIS transforms that Microsoft has made available for download.  They have differing degrees of usefulness, and have been available for a while but I am putting them out here for future reference.  Off the top of my head, it looks like Regex would be useful, UnPackDecimal if you are deal with mainframe files, calendar transform (as I recall it has a dependency on a DLL from SSAS though).

 

Regex Transform
Regex is an SSIS dataflow component that applies a configured regular expression against an incoming column, matching, extracting, or splitting, as configured by the user.

ConfigureUnDouble Transform
ConfigureUnDouble takes a text column and removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes. Configure the quote character by, on the first tab of the advanced editor, setting the "Qualifier" custom property to the desired value.

RTrimPlus Transform
RTrimPlus takes a string or unicode column and removes trailing spaces, whether ASCII or Japanese.

UnpackDecimal Transform
UnpackDecimal takes an input column formatted in packed decimal (comp-3) and generates the corresponding Decimal value.

UnDoubleOut Transform
UnDoubleOut is an SSIS dataflow component that removes qualifiers from quoted text, either in place or via the creation of a new output column.

CodePageConvert Transform
CodePageConvert is an SSIS dataflow component that translates from and to any code page or unicode character representations.

SeeBuffer Transform
SeeBuffer is an SSIS dataflow component that sits in a data flow and is provided a look at each buffer that is presented to it.

UnDouble Transform
UnDouble takes a text column and removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes.

NullDetector Transform
NullDetector is an SSIS dataflow component that sits astride a data flow, and, depending on whether the value of a user-indicated column is null or not, routes rows to one or the other of its outputs.

Calendar Transform
CalendarTransform is an SSIS dataflow transform component that generates standard calendar attributes

Microsoft Performance Point Server 2007 finally RTM's

After years of hard work and a ProClarity acquisition later, Performance Point Server 2007 has finally been released.  More details here on the product home page.  Here are links to trial downloads and SDK.

PerformancePoint Server 2007 Evaluation Version (x86)

PerformancePoint Server 2007 Evaluation Version (x64)

Deployment Guide, Operations Guide, Whitepapers

Planning Server, Business rules development guide and Monitoring SDK

Documenting a SQL Server 2005 Solution end to end

I had heard of this tool before, and it looks interesting if you ever need to document a full SQL Server 2005 solution.  It apparently documents SSIS, SQL and SSAS as well as SSRS.  The output shows data lineage, as well as the structure of your database.  Worth a look, pretty affordable too.  Too bad they couldn't make a version that worked as an add-on to Data Dude.  Here is the link to their homepage.

Taking the pulse of SQL Server

If you have ever been involved in a big SQL Server project, you might have wanted some easy way to figure out how things are performing on the server.  Fortunately, many other people have wanted to do the same.  There are several ways available now (and more to come in SQL 08, specifically for SSAS I believe).  You can use the SQL Server Health and History Tool (SQL H2) which tells you all kinds of interesting things about the internals of SQL and run reports against it.

The other new and cool thing that has come out is an actual data mart that is built on top of the man Dynamic Management Views (DMV) inside SQL Server.  The DMV's are what drive the reports inside SSMS, and you can use them to find out all kinds of things.  Now you no longer have to run them manually.  Thanks to the SQL Server Customer Advisory Best Practices Team, now you can just install SQL DMVStats Data Warehouse on your server and your good to go.

Here are the main features as listed on CodePlex:

• DMV data collection
• DMV data warehouse repository
• Analysis and reporting.

VSTE for DB Pros (Data Dude) Power Tools released

If you are using VSTE for DB Pros, you will probably want to pick up the newly released Power Tools (didn't they used to call these sorts of things "Power Toys"?).  It adds a lot to the product in the way of convenience with the dependency viewer (you don't have to pretend to rename something to see dependencies now), additional refactoring abilities, enhanced data generation and T-SQL code analysis among other things.  Download it here.

Read more about it at Gert Drapers' blog, including detailed explanations and screen shots.

Here's the list of new features, copied from Gert's blog:

• Dependency Viewer
• Refactoring
  • Move Schema
  • Expand Wildcard
  • Fully Quality Name
  • Refactor in to strongly typed DataSet definitions
  • Refactor Command Generator
• Data Generation
  • Sequential Data Bound Generator
  • Editors for the Data Bound Generator, Sequential Data Bound Generator and RegEx String Generator to make configuration easier
  • The RegEx editor also tries to interpret your CHECK CONSTRAINTs and create a matching RegEx expression that you can use to generate data values that match the constraint definition
  • The RegEx editor can also be used for interactively defining and testing RegEx expressions and evaluate the output visually, which makes it a lot easier to create the right RegEx expression for your value domain.
• MSBuild Tasks
  • SqlSchemaCompareTask; allows you to compare schemas between two database from the command line using MSBuild.
  • SqlDataCompareTask; allows you to compare the content of tables within two databases from the command line using MSBuild.
• T-SQL Static Code Analysis
•  Miscellaneous tools
  • SQL script pre-processor command-line utility, which will expand all SQLCMD includes and variable definitions (sqlspp.exe)
• Schema Manager API

SR1 for Visual Studio 2005 Team Edition for Database Professionals (DataDude)

This news is about a month old, but the RTM version of SR1 for VSTE for DB Pros has been been released and is available for download.  One of the most important things that it adds is the ability to have "database references", which is a 3 or 4 part reference to a database other than the one that you are currently working with.  Much-needed functionality.

Interesting AJAX enabled SSAS client

I was checking my webmail today, and one of the ad-links caught my eye.  It was for an AJAX based SSAS client.  I clicked the link and it took me to IntraSight, by a company called Active Interface.  Screenshots are available at that link.  There are a lot of interesting SSAS web based interfaces floating around out there if you look hard enough.  Add this one to the list.  SharePoint integration is listed as one of the bullet points.  Pricing is pretty affordable: $1995/server.  It might be worth a look if you need a web-based OLAP tool.

Here is their feature list:

• IE browser-based SSAS client
• High-fidelity AJAX user interaction
• Interactive table and chart views
• Annotated public and private bookmarks
• View and edit generated MDX
• Sharepoint integration
• Excel, Word, and PowerPoint generation
• Publish static HTML site from bookmarks
• Web tier caching
• Search dimensions
• Filter member sets
• Sort and filter measures
• Specialized business-centric and industry-specific views

Creating SQL 2005 Data Warehouse Metadata

One of my favorite tools to use when designing a data warehouse is the Dimensional Modeling Spreadsheet created by Joy Mundy and Warren Thornthwaite of the Kimball Group in conjunction with their must-have book, The Microsoft Data Warehouse Toolkit.  Not only does it allow you to fully build out your fact/dimension design, ETL logic, source-to-target mapping and more, it also generates a script that will create the first iteration of your data model once you have everything in place.  I always try to fully flesh out this spreadsheet before I create the first SQL table, it really helps get things off to a good start.  As an added bonus, Joy and Warren have a whole site full of useful tools and utilities for building a Data Warehouse.

Mark Garner has just released Beta 1 of a tool called Metashare.  He appears to have based it on the Kimball Group spreadsheet I mentioned above, and pulls the same data back out of your data model for the purposes of documentation.  I will look forward to checking out Metashare with the next DW that I build.