Building Packages Programmatically…

…just got easier.

One of the most common SSIS questions that most consistently gets the most consistently frustrating answers is "how do I build a package that can load data from an Excel spreadsheet into a database table when I don’t know the layout of the spreadsheet until run time?"

No, the question is never phrased quite this succinctly, but there are dozens of variations that all boil down to this core. The source may be a text file or a database table and not an Excel spreadsheet, but the "I don’t know the schema until I run the package" or "the source columns map directly to the destination columns, but I don’t know exactly what they are" aspects of the question remain the same.

And the answer remains the same too: "SSIS doesn’t do ‘dynamic’ data flow. You can work around this limitation by using the SSIS .NET API to dynamically build and execute a package, reading in the metadata about your source and destination columns to construct the data flow."

The problem with this answer is threefold:

  1. The API for working with the SSIS data flow is a bit complex.
  2. The documentation on the SSIS data flow API is a bit sparse.
  3. The samples available that demonstrate this technique are a bit nonexistent.

This triumvirate of frustration has been a pet peeve of mine for quite some time. In fact, I’ve been known to say that such a frustrating answer to such a common question is a major barrier to the adoption of SSIS.

But this week the story just got better. The SSIS team has released additional functionality (see Matt Masson’s blog post for more details on the new functionality, including SharePoint List Adapters!) as part of their SSIS Community Samples project on CodePlex. As part of the new release, the samples now include a package generation application that demonstrates how to solve this archetypal problem. Here’s the intro blurb from the readme file for this sample:

"This sample demonstrates package generation and execution using the Integration Services object model. The sample can be used to transfer data between a pair of source and destination components at the command line.

The sample supports three types of source and destination components: SQL Server, Excel and flat file. You can choose to create a new destination based on the source component metadata. Alternatively, this sample supports mapping source and existing destination columns by using the same column names or manually, by using the command line.

You can modify the code in this sample to fit your own application."

This may not sound exciting when you read it, but it almost takes my breath away. Digging into this sample is high on my to-do list in the weeks ahead (July and August have been crazy months for me, as the lack of activity on my blog demonstrates) and if you have ever been faced with this "dynamic data flow" conundrum, you should check it out as well.


About ssimagine

My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
This entry was posted in BI, Katmai, SQL Server, SSIS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s