SSIS Package Templates: The Good, The Bad and The Ugly.

This is one of those “I could have sworn I blogged on this topic years ago” posts. I speak to this topic whenever I’m presenting on SSIS development best practices, and with my half-day mini-seminar in Stockholm last week[1] I was thinking of it – and speaking about it - again. Although SSIS is a great platform and provides pretty much all the tools you need to get the job done, sometimes it’s not always easy to know what tools to use and how to use them.

Which brings us, of course, to package templates. So before we continue, please read this Knowledge Base article on how to create a package template in BIDS.

Done? Good – let’s review, and paraphrase while we do it.

Package templates are pretty much the entire reuse story in SSIS.[2] You define the baseline package, including connection managers, log providers, configurations, variables and expressions, tasks, containers and precedence constraints and sometimes data flow components as well. When you create a new package you create a new copy of the template with this pre-defined starting point instead of starting with a blank package, so you can be sure that the package is just like the baseline you created.

In keeping with the theme established in the post title, this is the “good” side of SSIS package templates. They’re valuable, and when you’re building a large number of packages for the ETL subsystem of a DW/BI project, they’re invaluable, in that the common “plumbing” required by all packages can be defined once and used consistently everywhere. And this is particularly vital when working in a team environment, where different developers (each of whom inherently has his own style and own way of building packages) are working on the same project, which is typically the case on ETL projects of anything but the most trivial in scale.

Which brings us to the “bad” side of package templates, at least as implemented when following the guidance in the KB article referenced above. This guidance is firmly targeted at the lone developer; did you notice how all the instructions about creating a package and saving it in the Visual Studio install folder never mentioned on what computer you should be performing these steps? That’s because the article assumes the context is “your machine” – the lone developer’s machine or - at scale – on every developer’s machine.

Why is this bad, you ask? Surely it can’t be so much work to copy a DTSX file to the same folder on a few computers, can it?

Of course not. The “bad” side of following this approach comes not when creating the templates, but when the real world comes into play and the project continues to grow and evolve. In a perfect world, when the needs of the project are understood 100% before development begins, and the needs of the project never change after development begins, this approach will work just fine for team development. Your mileage may vary, but I’ve yet to work an SSIS project[3] like this.

In the real world, most successful BI projects are iterative and agile, just like most other successful development projects. If you look at SQL Server MVP John Welch or MVP and VSTS Ranger Ted Malone, their blogs focus strongly on agile BI. If you read Kimball, you will see an overarching theme of agile and iterative development. And if you’ve ever seen former SSIS program manager Michael Entin present on SSIS performance tuning[4] you’ve noticed this best practice under the heading of “Principles of Good Package Design”: “Follow Microsoft Development Guidelines - Iterative design, development & testing.”

Ok, so why did I just go off on that tangent and drop so many names? Regular readers will quickly note that my writing style practically revolves around tangents and will likely seize on this as the reason, but today it is to drive home a point: The real world is neither pretty nor neat, and people who know more about BI than I will ever forget[5] have gone out of their way to point this out.

Why is this bad, you ask yet again? Surely it can’t be so much work to copy a DTSX file to the same folder on a few computers, can it? And can you please just get to the point, Matthew?

Maybe. Picture this story:

A small team of five or six SSIS developers are working together on a project. The lead developer has created a package template for all developers on the team to use moving forward. He emails the DTSX file to each team member along with a reference to KB article 908018 for instructions on how to install and use the template.

A week later the lead developer identifies a problem with the package template.[6] He updates the template package and emails it out to his team with instructions to update their local machines with the new package right away.

Unfortunately, one of the team members is a “heads-down” kind of developer who turns off email during the day, and finishes a few more packages before he gets the message, even though he does save the new template package once he checks his email.

And another developer is on vacation when the email is sent, and by the time he gets back he has too many new messages in his inbox to ever get through, so he decides that if anything is really important then the sender will re-send it, and he just deletes anything that is unread to get a fresh start with no email backlog.[7] So he keeps on developing packages using the outdated template until the next update is sent out a few weeks later…

You see where this is going, right? This is gong to the “ugly” side of SSIS package templates. The ugly part comes in downstream when integration testing begins. The packages developed by the individual team members need to be deployed into a test environment and tested together, and of course at that point things start to fall apart. Everyone works extra hours and deadlines slip by while people try to track down problems they knew had been solved long ago. Although to be honest, the worst case scenario is that the test cases don’t find the problem at this point, and the ETL solution is deployed into production without the errors being resolved.

Unfortunately, I have seen this story play out in one way or another on several different projects over the years. The details are always different, but the ending is always the same: extra work, unhappy clients, and pain.

Now picture this story retold, with a few different details:

The lead developer works with his team to establish Team Foundation Server (TFS) source code control[8] before any development begins. He develops a set of packages that serve as “template examples” and checks them into TFS.

He also establishes standards with his team, so that each team member knows to Get Latest whenever starting work on a new package. This ensures that – no matter what happens – each developer always has the most recent versions of all packages with no need for “special case” tasks like updating a template.

The lead developer also instructs each development team member to create any new packages by right-clicking in Solution Explorer and selecting Add Existing Package, and then browsing to a current file from TFS.[9] This ensures that whenever a new package is created, it is being created as a new instance of a “rolling template” that is stored in TFS and as such the same version is always available for use by all developers.

Then, whenever problems are identified in existing packages, the “problem packages” are updated immediately, and the fixes are applied to any existing packages created from a now-outdated template.[10]

And whenever anyone goes on vacation, problems are found earlier, their impact is lessened, deadlines are met, and everyone lives happily ever after.

Ok, so the results are probably not so extreme, but in my experience on multiple projects this “rolling template” approach is the best practice way to approach SSIS package templates. Any attempt to utilize the Microsoft guidance in a team environment runs into problem after problem, and at the worst extreme I have seen a development team manually re-implement dozens of SSIS packages using a new template after churning for a full week while trying to reconcile and resolve the problems disseminated by using the “official” template approach.

And to be fair, the definition and implementation of policies and procedures can help make following the “official” approach less bad, but the starting point is inherently flawed for team development. If anyone were to suggest to me that following KB 908018 was the right way to approach package templates, I would have to politely[11] disagree.

What do you think? Even though I have been around the block a few times with SSIS, I don’t claim to know it all. I’d love to hear your thoughts on how you use package templates – especially if I took your name in vain earlier on - including what works and what does not work in your team environment.

 

[1] I never seem to find the time to do tourist stuff when traveling for work, but for some reason I just could not say no when I was invited to speak on SSIS best practices when in Sweden for a friend’s wedding. I have the world’s most understanding wife.

[2] I say “pretty much” because using small, modular packages and executing them via the Execute Package task or some other client, or using the SSIS .NET API to dynamically construct and execute packages are the other parts. In SQL Server 2008 and SQL Server 2008 there is not a lot to say about reuse in SSIS.

[3] Or projects using any other technology, for that matter. If you know of any projects like this and are looking for a consultant, I’m pretty sure I can work out a moonlighting arrangement with Microsoft. ;-)

[4] Or have seen me blatantly plagiarize his slide decks for my own talks.

[5] No Ted, this doesn’t include you. Sorry.

[6] I won’t go into the details of the problem because it isn’t really important to the story, but it could be anything along the lines of:

  a) A missing variable, which needed to be added.
  b) A variable defined at an incorrect scope, hiding a variable at the package scope when accessed from a Script task.
  c) A log provider needed to be added to support new project requirements.

You get the idea, right?

[7] Yes, people really do this. Yes, I’m thinking about doing it right now. I’ve been on vacation for a week and have been checking email at least once a day to respond to high priority items, but I still have nearly 700 unread messages in my inbox. ;-(

[8] Or Visual SourceSafe or pretty much any modern SCC system. But TFS has so many additional benefits, it’s hard to recommend anything else.

[9] Just to be clear, I’m not implying that there is any ability to browse TFS repositories in BIDS, but instead am saying that the developers all have the most recent packages from TFS and can browse to them on their local file systems.

[10] Odds are they use a tool like PacMan to batch update the existing packages. ;-)

[11] Or perhaps not so politely depending on the hour…

Coolest. Tool. Ever.

I’m struggling with how to best start this post. Here are a few possibilities:

• I like to think that I’m a hard person to impress. But…
• I swear at my computer many times a day, but usually in anger and almost never in delighted surprise.
• Someone deserves a raise.
• Holy crap!

All of them apply, so you can take your pick.

But what am I talking about?

I’m talking about the DocumentReflector tool that’s included with the Open XML Format SDK v2.0.

You see, I’ve been playing today with a few Office development tasks today for a work-related project. I’ve never looked too closely at working with the Office Open XML file format before this week; I’ve read white papers and such, but until you start writing your own code you never really “get” what an API is all about. After a few false starts and many searches, I was less than impressed.

But then I discovered DocumentReflector. This is a stand-alone SDK tool that will (wait for it) let you open an Office Open XML document, select a subset of the document or the entire document and it (here it comes) displays the C# code that will create it.

Holy crap indeed.

I’m sure I’ll be swearing at this amazing tool before the weekend is over (the new wears off pretty quickly for me) but today whoever built this app is officially my hero.

Edit: I almost forgot to add – there’s a great overview of using this tool on Erika Ehrli’s blog, here.

Now I can’t help but ask why we don’t have a tool like this for SSIS packages. Wouldn’t it be great to point a utility at a DTSX file and have it output the code required to build that package through the SSIS API? ;-)

More on Package Configurations

Douglas Laudenschlager from the SSIS documentation team has posted an excellent article to his unofficial SSIS blog on how package configurations are applied in SQL Server 2005 and SQL Server 2008 Integration services. Check it out here.

I don’t often post on my blog simply to reference someone else’s original work, but I wanted to do it today because:

• Package configurations are a vitally important feature that are often misunderstood.
• The manner in which configurations are applied has changed between SQL Server 2005 and SQL Server 2008.
• These changes can cause changes in package behavior when executing packages with specific DTEXEC options

So if you are moving from SQL Server 2005 to SQL Server 2008, definitely be sure to read about these changes.

Visual Studio Window Layout for SSIS Development

My friend and colleague Lynn Langit has a habit of blogging where she posts an image and a brief question and that’s it. I’m very jealous, because I can’t seem to write a blog post that’s shorter than 20 pages or so.

So in this post I’m trying to be More Like Lynn.

This is how I configure my Visual Studio development environment for SSIS development:

(Click on the thumbnail for a larger image.)

How do you configure your windows?

Ok, so now if I were More Like Lynn, that would be the whole post. But since I haven’t had a mango martini in far too long, I feel compelled to elaborate:

1. I dock the Variables window in the upper left corner in its own tab group.
2. I dock the Toolbox window in the lower left corner in a different tab group.
   (This lets me see both the Variables and the Toolbox at the same time during package development, which is often very useful.)
3. I dock the Log Events window in the same tab group as the Toolbox, because you generally don’t use them both at the same time.
4. I dock the Solution Explorer window in the upper right corner.
5. I dock the Team Explorer window in the same tab group as Solution Explorer.
6. I dock the Properties window in the lower right corner in its own tab group.
7. I dock the Error List window on the bottom.
8. I dock the Output window on the bottom in the same tab group.
9. I dock the Pending Changes window on the bottom in the same tab group as the Error List and the Output windows.
10. I leave all of the designers in the middle, since this is where they always go. :-)

This layout gives me ready access to all of the tools I need, when I need them. And I would still love to know how you lay out your windows in your SSIS development environment…

SSIS Development and Source Code Control

In my personal best practices for SSIS development, I claim that we need to treat SSIS packages like code. When I give presentations on this topic, I have a slide that elaborates and talks about how we must use source code control for our SSIS projects just like we do[1] for our “traditional” VB and C# projects. It’s one of those irrefutable best practices of software development; you wouldn’t drive your car without your seat belt, and you’d never even think of building software without source code control.

In fact, this is so obvious and so important, I had assumed that I must have blogged about it years ago. So I came here tonight to get the URL so I could reference it somewhere else.

And I couldn’t find it. So here’s the post I was expecting to find,[2] so that the next time I look I will find it here.

The points that I like to bring up in my presentations are how even though SSIS development is software development, there are inherent differences in the tools that can turn into “gotchas” if you’re not careful. In particular, walk through these basic steps just to make sure you’re following me:

1. Open an SSIS package in Visual Studio – a package that has at least one user variable defined.
2. Look up at the tab at the top of the package designer. You’ll see that the tab displays the name of the DTSX file followed by the word [Design] in square brackets, in case you weren’t sure you were looking at the designer and not the raw XML.
3. Ensure that there is no “*” asterisk after the word [Design] – this asterisk shows that the file has been modified and needs to be changed, and for the purposes of this exercise we need it to not show up. If your package does show an asterisk, save it now to make the asterisk go away.
4. In the Variables window, click on the name of a variable. Don’t change anything, just click.
5. Then click on the design surface. Again, don’t change anything – just click.
6. Then look up at the tab. What do you see?
7. That’s right! An asterisk!! The package has been changed by a non-editing action on your part. Has the world gone mad?!?

Um… probably not. Or at least, not yet.

But let’s assume for the sake of argument that you followed this best practice and have been using source code control. And let’s further assume that since no one told you any different that you set up your source control plug-in in Visual Studio and left it with the default settings.

Uh-oh.

Let’s take a look at those default settings, shall we?

That’s right. The default settings are evil.

Why evil? Well, think back to the “not really editing a variable name” scenario from earlier. If this package were under source control, you just checked out that package without meaning to, and probably without realizing it. And that means that as soon as you walk out the door to go on that two-week vacation[3], you just became that guy. That’s right – the guy who gets blamed for things because he’s not there. Because the default source control settings in Visual Studio are to silently check out a file when the developer starts editing it. And while this may make sense for traditional code files, it definitely does not make sense for DTSX files.

And the worst part is, you really are to blame here, at least in part, because you did not resist, you did not fight back against the evil that consumed you and is now ravaging your team’s productivity. You’re kind of like Gollum.[4]

But do not despair – it is not too late to turn back from the path of evil! In fact, you really just need to change one drop-down, like so:

That’s right – just say “Prompt for exclusive checkouts” and the problem goes away[5]. Any time that you perform a step in the SSIS designer that would cause the package to get checked out (we walked through one with the variable name thing, but there are many more) you’ll be prompted to check out the file.

And that’s about it. As usual, I spent five pages to say what could have been done with one screen shot and ten words, but hopefully you’ll understand the context a little better this way.

So always use source code control, but also be certain that you have configured it correctly for SSIS.

[1] We do, don’t we?

[2] Yes, it’s late on a Saturday night, but hopefully you too will see that this is more than a little like time travel. Or is it just me?

[3] I did mention that you are going on vacation, right? Go on – you’ve earned it!

[4] Did I mention how much I loved you in those movies?

[5] Yes, this means that Sam and Frodo’s budding romance will probably never get a chance to bloom, but that is a price I’m willing to pay for you to enjoy your vacation.

Christmas Came Late!

Santa has just delivered the gift that many SSIS developers have had on their Christmas lists: more samples and options for programmatically building SSIS packages. Although in this case, the role of Santa is played by Matt Masson of the SSIS team, who must have gained quite a bit of weight since the last time I saw him. Matt posted seven new articles on his blog yesterday, including this index post here: http://blogs.msdn.com/mattm/archive/2008/12/30/samples-for-creating-ssis-packages-programmatically.aspx

There are samples for building a package with a data flow task, and for adding OLE DB Source and Destination components, adding the ADO.NET Source component, adding a Row Count transformation and adding a Conditional Split transformation. Each post includes nicely commented C# code, and each one goes a long way towards filling the documentation gap around the SSIS data flow API.

And if that's not exciting enough, Santa was just getting started. Evgeny Koblov, a tester on  the SSIS team has gone far beyond simply it easier to work with the less-than-intuitive COM API exposed by the SSIS data flow. He has built a better API. It's called EzAPI and you can read about it on Matt Masson's blog here: http://blogs.msdn.com/mattm/archive/2008/12/30/ezapi-alternative-package-creation-api.aspx. You can also download it and start using it on the CodePlex web site here: http://www.codeplex.com/SQLSrvIntegrationSrv/Release/ProjectReleases.aspx?ReleaseId=21238

EzAPI is probably the most exciting thing I've seen coming into the world of SSIS since the release of SQL Server 2008, if not before. It's essentially a native .NET wrapper around the underlying COM API, which doesn't sound particularly interesting at first glance, but since it delivers the ability to quickly and easily build SSIS packages and data flows through code, it's sure to be a time-saver (if not life-saver) for many SSIS developers.

Now please excuse me while I download EzAPI and start to play...

Connection Strings

This is just a little reminder for myself as much as for anyone else. There's a great resource online at http://www.connectionstrings.com/ that lists the syntax for hundreds of different types of data sources (SQL Server, Oracle, Excel, Active Directory, etc.) complete with examples and general rules/guidelines for constructing them. If you're ever in a situation where you need to build a connection string without a GUI to help you, I've never seen a better reference. Check it out.

Silverlight 2.0 RTM!

This is a little far from the usual BI topics, but I think it's pretty exciting regardless. Microsoft Silverlight 2.0 has[1] been released. You can download the bits and the development tools on the official Silverlight web site. There is also a great blog post from Scott Guthrie that describes the 2.0 platform and lots of resources that are available to Silverlight developers.

I don't know about you, but as a developer who has to play in the UI tier occasionally, this is the release I've been waiting for. Check it out!

[1] Finally! I can't remember the last time there was a product that I'd been hearing about and seeing so much of for so long before it was released.

One More Reason to Attend

I've posted a few times already[1] about the SSWUG Business Intelligence vConference that I have been helping to organize. Well, the conference is now less than a month away, and there is more news to share:

We're giving away a copy of Microsoft Visual Studio Team System 2008 Team Suite with MSDN Premium to one lucky attendee.

That's right - the big one. This is the ultimate version of Microsoft's MSDN subscription, with a suggested retail price of $10,939. If you're a software developer or BI professional, this package has everything that you need to develop for the Microsoft platform, and then some.

If you'd like a chance to win this MSDN subscription, just register for the SSWUG Business Intelligence vConference. For just $100 you get:

• 30 amazing technical sessions from 10 amazing speakers.
• The ability to watch the sessions either during their multiple scheduled timeslots, or on demand for up to 14 days after the conference is over.
• Online chat and Q&A with the speakers during the scheduled sessions.
• Downloadable slides, transcripts and demo files so you can continue the learning once the session is done.
• And here's the one that blows my mind - a $150 discount on the Fall SQL Connections conference in Las Vegas. So if you're planning to attend the SQL Connections conference, the SSWUG Business Intelligence vConference more than pays for itself.

So what are you waiting for? This vConference is going to be amazing, and we'd love to see you there!

[1] For example:

• BI for the Virtual Guy
• Check Out This Lineup!
• And also a mention in What Happens in BIDS, Stays in BIDS

SQL Server Sample Install Epiphany

(Warning - this post has turned into a long drawn-out rant. If you want to skip to just the useful stuff, scroll down to the third and final bulleted list way down there at the bottom. I won't mind, I promise.)

One "new feature" of SQL Server 2008 that has always seemed of dubious value (at best) to me is the way that the product samples have been removed from the actual SQL Server installer. If my memory serves me correctly[1] the history of SQL Server samples (namely the sample databases) has gone something like this:

• SQL Server 2000 and earlier: Sample databases automatically installed with the RDBMS. Users must manually delete them post-install if they're not wanted.
• SQL Server 2005: Sample databases part of RDBMS install, but are not installed by default - they must be manually selected by the user.
• SQL Server 2008: Sample databases not included with the RDBMS installer at all. Users must wade through dozens of poorly-documented downloads on the CodePlex web site, hope they get the installers that include the databases that they need, install them, then struggle to find out what files the installers put where.

Ok, so perhaps that last bullet isn't particularly fair[2] but it does sum up my personal experiences with getting samples working with SQL Server 2008. If you go to the Releases page for the SQL Server 2008 samples project on CodePlex you'll see 28 (twenty eight!!) different MSI installers that you can download. And when you install any one of them, it's pretty much a mystery what files are installed and where you can find them. In my book this is quite a big step backward.

To be completely open, I realize and admit that I'm not the typical SQL Server user. I do a lot of training, presenting and writing on SQL Server topics, and the samples are a big part of these activities - because without them I'd have to build samples of my own. And of course once the SQL Server samples are installed, they're great - it's hard to find anything bad to say about the content itself.

Anyway, today I've been spending some time preparing for a few presentations that I have on my schedule in the next month or so, and have needed to go back out to the CodePlex web site to download (again) the SQL Server 2008 samples. When I was faced (again) with the 28 (twenty eight!) different installers, I groaned and hung my head. "Why?" I moaned, "Why can't they just give us the expletive expletive SQL scripts and source code instead of these accursed MSI files?!?!?"

Oh.

Oh yeah.

Oh yeah, one of the installers is named "SQL2008.AdventureWorks_All_DB_Scripts.x86.msi" - that sounds useful. How could I have missed this?

In fact, I've found that to get to where I need to be, there are really only two things I need from CodePlex:

• That SQL2008.AdventureWorks_All_DB_Scripts.x86.msi file, which you can get here.
• The "All Microsoft Product Samples in a Box" download, which includes "all Microsoft SQL Server product samples (except for the sample databases, due to size constraints) and does NOT include any community projects" and which you can get here.

The nice thing about this second download is that you can choose to download it as a zip file, which means you can extract it to wherever you want to put it. And that, for me, is key.

The DB installer MSI is another matter entirely. When you install it, there is no indication of where it's putting the DB scripts, nor does it give you the option to choose a destination directory. I looked in the C:\Program Files\Microsoft SQL Server\100\Samples folder - that makes sense, right? Wrong. There's nothing there but a license, a readme file which references the C:\Program Files\Microsoft SQL Server\100\Samples folder where the samples aren't located, and a shortcut to the CodePlex project.[3] Ugh.

Instead, the samples are installed in the C:\Program Files\Microsoft SQL Server\100\Tools\Samples folder (note the inclusion of Tools in the folder path) where, if you're like me, you'll never think to look.

Ok, this post has turned into a rant, which was really not my intent. Please let me summarize:

To get the complete samples for SQL Server 2008, perform the following steps:

• Download SQL2008.AdventureWorks_All_DB_Scripts.x86.msi from http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=16040.
• Download SQL2008.All_Product_Samples_Without_DBs.zip from http://www.codeplex.com/SqlServerSamples/Release/ProjectReleases.aspx?ReleaseId=16037.
• Run the MSI installer and then look in the C:\Program Files\Microsoft SQL Server\100\Tools\Samples folder to find the database scripts. Run them as you wish.
• Extract the zip file into the directory of your choice.

Hopefully this will help someone out there avoid the frustration I've felt from time to time when working with the "decoupled" samples...

[1] If you say this in the voice of Chairman Kaga it sounds like a cool pop culture reference, instead of just an admission that I have trouble remembering things that happened before I started typing this blog post - try it out and you'll see!

[2] Especially seeing the huge improvements that the samples owner David Reed has made over the last few months leading up to SQL 2008 RTM.

[3] No, I have not yet filed a Connect item on this readme file. Typing up this rambling blog post took so long that I didn't have time to actually file a useful bug as well...

Waltham Code Camp 10: "Dev InTENsity!"

I just got the announcement today, so I guess I'm behind the times as usual - the 10th Code Camp event is going to be held next month at the Microsoft offices in Waltham, MA on September 20 and 21.

In case you missed Code Camp 9 in April, this is going to be a free two-day technical conference focusing on Microsoft developer technologies. The last time around there were over six hundred attendees who came from as close as Boston and as from as far as hundreds of miles way, and (if my memory serves me correctly) there were six or seven different tracks going on throughout each day. People presented on topics ranging from .NET to Silverlight to SQL Server Integration Services. (Guess who did the SSIS sessions? ;-)

Sadly, my travel schedule will not allow me to be in Waltham to speak at next month's event, but if you are in the northeast US, it's well worth the trip to come.

For more information, check out Chris Bowen's blog. He's one of the event organizers and he has all of the details, whether you want to attend or present, or both.

Visual Developers of Upstate New York

I apologize for the late notice (I've know for weeks, but have kept forgetting to post) but I will be speaking next Wednesday, August 27th, at the VDUNY user group in Rochester, New York. This user group meets on the 4th Wednesday of each month in the Rochester Microsoft offices, and generally has a great turnout of talented software development processionals.

This month I'll be presenting on some of my favorite new developer-centric features in SQL Server 2008. I realize that this is awfully vague, but that is intentional. I'm planning on going in with a loose agenda and lots of demos, and seeing where the session goes.

And to sweeten the pot a little, I will also be giving away a complete set of nine DVDs with all of the content from the TechEd Developers and IT Professionals conferences in Orlando this June. If you're in the Rochester area, you should plan on attending - it will be a lot of fun.

BIDS Helper 1.3 Release

Just a quick update on this hectic weekend day: The long-awaited 1.3 release of the best and most important add-in for Microsoft BI developers - BIDS Helper - has been released. It has a plethora of new features and supports both SQL Server 2005 and SQL Server 2008.

Download it here: http://www.codeplex.com/bidshelper/Release/ProjectReleases.aspx?ReleaseId=9557

Now.

File Name Expressions

A recent post on the SSIS Forums on MSDN reminded me how anti-intuitive it can sometimes be to work with SSIS expressions. For example, it's quite common (such as when using the File enumerator for the Foreach Loop container) to have a package variable that contains the fully-qualified file name of a file. But what if you need only a portion of the file name?

Well, the obvious answer (as I mentioned in an earlier post on expressions) is to look at the SQLIS.com Wiki for expression examples, because there are some good examples up there, including these first two. So here are a few more, each of which includes sample output for this sample input:

C:\Projects2008\RC0_SSIS_Test\RC0_SSIS_Test\FileExpressions.dtsx

Get the filename from a full file path:

RIGHT( @[User::FileName], FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1 ) - 1 )

Output: FileExpressions.dtsx

Get the directory from a full file path:

SUBSTRING( @[User::FileName], 1, LEN( @[User::FileName] ) - FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1 ) )

Output: C:\Projects2008\RC0_SSIS_Test\RC0_SSIS_Test

But what if your needs are covered there? Well, here are a few more examples:

Get the file extension from a full file path or file name:

RIGHT( @[User::FileName], FINDSTRING( REVERSE( @[User::FileName] ), ".", 1 ) - 1 )

Output: dtsx

Get the file name minus the extension from a full file path:

SUBSTRING (@[User::FileName], LEN( @[User::FileName] ) - FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1) + 2,  LEN (RIGHT( @[User::FileName], FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1 ) - 1 ) ) - FINDSTRING( REVERSE( @[User::FileName] ), ".", 1 )  )

Output: FileExpressions

Expressions are probably the single most powerful tool in the SSIS developer's toolset, so the more you use them the more you're likely to love them. But sometimes having a few good examples is the best way to get over the initial learning curve.

Splitting a Delimited Column in SSIS

A recent post on the MSDN SSIS Forums has reminded me that I've been meaning to post on this technique for quite a few months now. The basic problem looks like this: The input rows in a data flow contain a key field and a "list field" whose contents are made up of a delimited list of values. Like this:

KeyField    ListField
1           a,b,c
2           b,c
3           b,d,e
4           c,f,h
5           a,b  

And the output needs to look like this:

KeyField   ListItem
1          a
1          b   
1          c
2          b       
2          c
3          b
3          d
3          e
4          c
4          f
4          h
5          a
5          b

The bad news is that there isn't any magical "Split Transformation" in SSIS. There are enough variations on data types and delimiters that it would probably be difficult to implement this as a transformation component. The good news is that it is trivial to implement this as a Script Component within the package. Here's how:

1. Start by adding a data flow task to your package, and adding an OLE DB Source component (that returns the input data above) to the data flow. Then add a Script Component to the data flow as well.
2. When prompted to select the type of component you want to create with the Script Component, select Transformation - it's the default, so this should be super easy.
   
   
   
3. Next, connect the data flow path arrow from the Source component to the new Script transformation. Because the SSIS data flow is so heavily dependent on metadata, we can't really do anything meaningful with the Script Component until it knows what input data it will receive.
   
   
   
4. Next, right-click on the Script Component and select Edit. In the Script Transformation Editor dialog box, select both columns to make them available as input columns so they are available in the Script code.
   
   
   
5. Next comes the one "tricky" part: marking the transformation as asynchronous. Because the transformation will be producing more output rows than it receives input rows, this is a vital step that is sometimes missed by inexperienced SSIS developers. Mark the transformation as asynchronous by setting the SynchronousInputID property of the output to None.
   
   
   
6. Now, because we are building our own output buffer (one of the characteristics of an asynchronous data flow component) we need to add the columns that the output buffer will contain. Select the transformation's output, click Add Column and set the properties (mainly the name and data type) appropriately.
   
   
   
7. Finally, what we've all been waiting for: the code. Click on the Design Script button and add the code to perform the actual split.
   
   
   
   

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim keyField As Integer = Row.KeyField
    Dim itemList As String = Row.ListField
    Dim delimiter As String = ","

    If Not (String.IsNullOrEmpty(itemList)) Then

        Dim inputListArray() As String = _
            itemList.Split(New String() {delimiter}, _
            StringSplitOptions.RemoveEmptyEntries)

        For Each item As String In inputListArray
            With Output0Buffer
                .AddRow()
                .KeyField = keyField
                .ListItem = item
            End With
        Next

    End If

End Sub


8. Once the code is in place, the last thing we need to do is to add a Row Sampling transformation (this one is handy because if it has no output data paths, it doesn't really do anything, and it doesn't require any configuration) and connect the output from the Script Component to it, with a Data Viewer attached. This will let us easily examine the output records from the script.
   
   
   
9. Now, when you execute the package, you can see the split output data as desired.
   
   

Well, that ended up having more steps (and more pretty pictures) than I expected it to, but I stand by my initial assessment of this as being a trivial task, once you've seen how the Script Transformation works. And if this still seems complicated to you, make sure you check out The Rational Guide to Extending SSIS 2005 with Script by Donald Farmer. It's an excellent book that anyone who expects to spend time writing script in SSIS should read.

New SSIS Project Templates in RC0

I've finally found the time to install SQL Server 2008 RC0 (although my SSIS/BIDS install is corrupted so I have not been able to run SSIS in RC0 through its paces) and noticed something interesting. I was creating a new C# project and noticed this:

Do you notice the two new project templates in the tree view on the left? That's right - Microsoft has added new class library (DLL) templates for creating custom Control Flow tasks and Data Flow components in C# and VB.NET. It's pretty unlikely that SSIS component development will ever approach the ease of use of the Script Task and Script Component, but having a Visual Studio project template that sets up much of the component plumbing is a nice step forward.

UPDATE: Upon further reflection (and a very helpful email) it turns out that these project templates are not designed for SSIS component developers. They are instead (as the project names should have clued me in, were I not so excited over my discovery) the templates that are used by Visual Studio Tools for Applications (VSTA) in the Script Task and Script Component inside a package. When you add a new Script Task or Script Component to your package, VSTA creates a new instance of one of these templates.

Did you Miss PacMan?

At the Microsoft TechEd conference in Orlando earlier this month I mentioned my PacMan project on CodePlex to several different audiences, including during each of my breakout sessions. PacMan is my "SSIS Package Manager" utility that I've built in C# to make my own life easier, but enough people expressed interest in it that I shared the utility online with hopes that it would make their lives less painful as well.

Well, although I may have succeeded to some extent, the very nature of PacMan probably limits its analgesic potential. This is because PacMan is:

• A "rough and dirty" development utility. This is code that I, as a developer, wrote for myself, as a developer, to use. This means that I was focused on solving short-term tactical goals, and not on building a general-purpose reusable framework for solving longer-term strategic goals. To put this another way, I expected to have to go in to PacMan and write a little code any time I wanted it to do something; I didn't have the time, energy or inclination to write all of that code up front.
• Largely undocumented. Other than the source code itself, there is little help for developers who want to start using PacMan for their own purposes.

Sadly, my schedule is highly unlikely to allow me to address the first point at any time in the foreseeable future. PacMan will likely always remain a "rough and dirty" utility, because the same work that keeps it valuable to me also keeps me too busy to refactor and refine it into something better.

But the second bullet is something I am likely able to do something about sooner rather than later. In fact, I plan on doing a little something about it today. Right now. Right Here:

PacMan Overview:

The whole point of PacMan is to perform batch operations on groups of SSIS packages. That's it. Because SSIS does not provide any built-in features for working with multiple packages at one time, this was something that I felt was sorely needed. Specifically, I needed a way to add a new variable to close to 100 packages. Obviously manually updating the packages wasn't an option, so PacMan was born.

The PacMan utility is implemented in a Visual Studio solution with two projects: a Class Library (DLL) Components project and a Windows Forms UI project:

The Components project implements a small set of classes that encapsulate access to objects in the SSIS .NET object model. The PackageUtil and PackageCollectionUtil classes are the two most significant ones, as we'll see later on.

PacMan UI:

The PacMan UI is exceptionally simple.[1] At the top of the form there are four options for selecting the scope of operations for whatever work is going to be performed - a single package, a single Visual Studio project, a Visual Studio solution containing one or more projects, or a file system folder containing multiple subfolders and packages. At the bottom of the form there is a set of tabs; each tab contains data entry controls for initiating a specific operation that will be performed on the packages that are in scope.

 

PacMan Components:

As mentioned above, the two main classes in the Components project are the PackageUtil and PackageCollectionUtil classes.

The PackageUtil class is essentially a thin wrapper around a Microsoft.SqlServer.Dts.Runtime.Package object. The PackageUtil class exposes a Package object through its SsisPackage property, and also exposes a set of properties and methods that make manipulating the package more straightforward.

The PackageCollectionUtil class is a List of PackageUtil objects, along with a set of properties and methods for manipulating the packages in the List.

When an operation scope is selected through the PacMan UI, an instance of the PackageCollectionUtil class is created and stored in the class-level packages variable which is in scope and available anywhere within the PacMan UI.

Most interesting scenarios in PacMan revolve around enumerating the packages collection and doing something[2] with each of the packages it contains.

Using PacMan:

The basic pattern of using PacMan goes something like this:

1. Get the PacMan code from CodePlex and open it in Visual Studio.
2. Add your own code to PacMan. You can do this either by adding a new tab to the UI or reusing an existing tab. There is an existing "Dev Workspace" tab that I use for experimenting or for one-off efforts when I don't want to be bothered building a UI for what I'm working on.
3. Feel good about accomplishing so much with so little effort.

Ok, so it may not be quite that simple all the time, but that's all I can think of right now. The nice thing is that the packages collection takes care of most of the hard work - all you need to do is worry about working on a single package at a time and PacMan does the rest of the work.

PacMan Use Case Example 1:

A typical example of using PacMan may look like this. The code below is used to rename a connection manager in all selected packages:

private void buttonSample_Click(object sender, EventArgs e)
{
    string oldName = "LocalHost.AdventureWorksDW";
    string newName = "AWDW";

    foreach (PackageUtil p in packages)
    {
        if (p.SsisPackage.Connections.Contains(oldName))
        {
            p.SsisPackage.Connections[oldName].Name = newName;
        }
    }
    packages.Save();
}

It doesn't get much simpler than that, does it? Imagine trying to reliably rename objects across a project or solution that contained dozens or hundreds of packages.

PacMan Use Case Example 2:

Some operations may require more code that what was shown above. For example, I recently needed to review a set of several hundred packages to ensure that there were no duplicate package IDs. To do this I updated the PackageCollectionUtil class to add a GetPackageIDs method that returns a SortedDictionary collection of the package IDs and the names of the packages that use them. The code looks like this:

public SortedDictionary<string, List<string>> GetPackageIDs()
{
    SortedDictionary<string, List<string>> ids =
        new SortedDictionary<string, List<string>>();

    foreach (PackageUtil package in this)
    {
        string id = package.SsisPackage.ID;
        if (!ids.ContainsKey(id))
        {
            // New key - add a new dictionary
            ids.Add(id, new List<string>());
        }
        // either way, add the package path
        ids[id].Add(package.PackageFilePath);

    }
    return ids;
}

Then, in the UI code, I could simply call this method and update a TreeView to display the results to the user:

private void buttonEnumerateIDs_Click(object sender, EventArgs e)
{
    if (packages != null)
    {
        SortedDictionary<string, List<string>> ids =
            packages.GetPackageIDs();
        BuildPackageIdTreeView(ids);
    }
}

private void BuildPackageIdTreeView(SortedDictionary<string, List<string>> packageIDs)
{
    treeViewPackageIDs.Nodes.Clear();
    foreach (KeyValuePair<string, List<string>> id in packageIDs)
    {
        if (!checkBoxShowOnlyDuplicates.Checked || id.Value.Count > 1)
        {
            // add a node for the ID
            TreeNode newNode = treeViewPackageIDs.Nodes.Add(id.Key);
            // add a child node for each package
            foreach (string packagePath in id.Value)
            {
                newNode.Nodes.Add(packagePath);
            }
            newNode.Expand();
        }
    }
}

Summary:

As you can see, PacMan provides a framework for developers to more easily perform operations on groups of packages. Those developers will still need to write code, but the scope and complexity of the code should be significantly reduced.

Please expect to see a few more PacMan-focused posts in the days and weeks ahead. There are some features I showed off at TechEd that can probably use some additional explanation, so now that I've laid the framework for further discussion, I can start work on those posts.

In the meantime, if you have any questions, comments or suggestions on PacMan, please feel free to post them here or to the discussion forum on the PacMan site on CodePlex. I can't guarantee that I'll respond to each one in a timely manner, but I'll do my best. Enjoy!

 

[1] I was going to write "ugly" here, because I know what it looks like. A UI designer I'm not.

[2] Yes, I suppose this goes without saying, as doing nothing is not a particularly interesting scenario, but the "something" in question up to the developer.

Hot SQL Injection

Normally when we think of SQL Injection attacks, we think about web applications, because this is usually where they raise their ugly head. But although this is the standard vector for SQL injection, we SSIS developers need to understand that there is danger in our ETL applications as well.

One of the techniques many SSIS developers (and I count myself in this group) often use is basing a package variable on an expression, and having that expression build SQL statements based on the values of other variables in the package. The risk comes in, of course, because if these "input variables" contain malicious text, they can create the same type of vulnerability as if the text were entered through a web form. So please, make sure that you understand what vectors for input exist, and that they are as secured as possible. Certainly the nature of most SSIS applications reduces their attack surface, but any time you're constructing a SQL string dynamically, some vulnerability exists.

I mention this today because the Microsoft Security Vulnerability Research & Defense blog had a great post a few days ago going into SQL injection in great detail. There's nothing SSIS-specific in there, but it should still be considered required reading for SSIS package developers who use this technique. Just because our ETL applications don't generally have a GUI, that doesn't mean we don't need to be concerned about poisoned input data.

Now, I should go back and finish watching The Breakfast Club...

Deploying and Testing Custom SSIS Components

SSIS provides two primary mechanisms for adding custom code to SSIS packages:

1. You can use the Script Task (for control flow) or the Script Component (for data flow) to add .NET code directly to your package.
2. You can develop your own .NET assemblies that use the SSIS .NET API to interact with the SSIS runtime to create custom tasks, transformations and more.

From my experience, the vast majority of SSIS developers tend to go with option #1. The Script Task and Script Component are relatively simple to implement, and you can take advantage of the .NET Framework base classes from your VB.NET code (or C# code starting with SQL Server 2008) without needing to worry about building and deploying a separate DLL to accompany your packages. Since the code is contained within the package itself, all you need to deploy is the package.

But therein lies the biggest drawback of using the Script Task and Script Component as well: the code is contained within the package itself. This means that if you have the same logic that needs to exist in multiple packages, then you need to copy it into each of them, which can quickly make for a maintenance nightmare if that shared logic needs to be updated.

And this is where custom components come in. Because they are defined outside the package their logic can be defined (and updated) in a single shared location and used in as many packages as necessary. Even though building a custom SSIS component is "real .NET development" it is relatively straightforward if you have .NET development experience. The SSIS object model defines a set of base classes and interfaces, and there samples included with SQL Server so it's easy to cheat during development.

But deployment and testing is often a different story. There are a few minor "gotchas" waiting in store for SSIS component developers:

• In order for SSIS to be able to use a custom assembly it needs to be added to the Global Assembly Cache - the GAC.
• In order for package developers to be able to use a custom assembly in Visual Studio, the assembly needs to be copied to a "special folder" in the SQL Server install directory tree.
• Because the assembly is a DLL, it cannot be run on its own for testing; it needs to be run in the context of another process, and because the assembly is an SSIS component that process really needs to be a running SSIS package.

Now if you're a developer who has experience building other types of .NET applications but have not build components to plug into other products (like BizTalk, Commerce Server - you name it) this may seem daunting. But fortunately this isn't the case - it's really as simple as pie.

First - the GAC. There is a command line utility called GACUTIL.EXE that is included with the Visual Studio SDK and the Windows SDK. On a computer with a standard installation of these two tools, you will find it in these two folders:

• C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin
• C:\Program Files\Microsoft SDKs\Windows\v6.0\Bin

Warning: Be certain not to use the version of GACUTIL that you may find in the C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322 folder, as this is for the .NET 1.1 version of GACUTIL and SSIS requires .NET 2.0.

Second - the "special folder." The actual folder will vary based on the type of component you're building, but they're all (on a computer with a standard install of SQL Server) subfolders of C:\Program Files\Microsoft SQL Server\90\DTS:

• \Connections
• \ForEachEnumerators
• \LogProviders
• \PipelineComponents
• \Tasks

As you can see, there's a folder for each type of component that you can develop.

Now that we know where the assembly needs to go, how do we get it there without needing to manually copy it each time you build the project? The answer lies in the project properties, on the Build Events tab:

In the Post-build event command line text box, enter the following text:

gacutil.exe  /if "$(TargetPath)"
copy "$(TargetPath)" "$(ProgramFiles)\Microsoft SQL Server\90\DTS\Tasks\$(TargetFileName)" /y

Please note that this example applies to a custom task - if you're building a different type of component, you'll need to put in a different folder path. Also please note that the example above does not include the fully qualified path for GACUTIL.EXE. This is because on my development machines I have added the C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin folder to the PATH environment variable so I don't need to type in the full path for the many helpful utilities in this folder. If you have not added this folder to your PATH, you will need to fully qualify this file.\

With those disclaimers out of the way, note what this Post-build event does: every time the project is successfully compiled, the project output (the custom task assembly in this case) will automatically be added to the GAC and be copied into the "special folder" where the SSIS designers in Visual Studio need it to be. We're good, right?

Right?

Well, there is still the question of testing and debugging. I'm sure that most developers always write perfect code the first time, but I always seem to need to iterate a few times to get my code just right. And in order to see the runtime behavior of an SSIS component is to add it to a package, and to run that package. Well, that's straightforward enough, but how to do that and still be able to set breakpoints in the component code so we can find those pesky bugs? Fortunately, the answer here is just as simple as before, and the place you enable debugging it in the project properties again - this time on the Debug tab:

There are two things you need to do here:

• Check the "Start external program" and enter the fully qualified path to DTEXEC.EXE in the text box
• Enter the appropriate arguments in the Command line arguments text box, to reference a package that uses the component you want to debug.

Then, it's as simple as debugging any .NET code: set breakpoints where you want to pause execution, and hit F5. Visual Studio will launch DTEXEC.EXE and attach to the new DTEXEC process. DTEXEC will load the package, which will in turn load and call into your custom component. Your breakpoints will be hit and you'll be able to step through your code to your heart's content.

See - as simple as pie.

Update -  01 June 2008: If you're developing your custom component on a 64-bit Windows computer, remember that the folder to which you need to copy your assembly is in the C:\Program Files (x86)\Microsoft SQL Server\90\DTS\ folder, because Visual Studio is a 32-bit application.

Checking the Value of a Variable in SSIS

It's a pretty common debugging and diagnostic task to need to check the value of a variable. The SSIS designers in Visual Studio do provide a set of debugging tools, but sometimes it's quicker and easier to get "low tech" and get the variable value yourself.

Back in the bad old days of VB 6 we would do something like this:

MsgBox g_VariableName

Back in the worse old days of ASP we would do something like this:

Response.Write(strVariableName)

And today in SSIS we can do something like this:

    Public Sub Main()

        Dim variableName As String = "User::VarTest"

        Dim vars As Variables = Nothing

        Dts.VariableDispenser.LockForRead(variableName)

        Dts.VariableDispenser.GetVariables(vars)

        Dim variableValue As String = CType(vars(variableName).Value, String)

        vars.Unlock()

        System.Windows.Forms.MessageBox.Show( _

            String.Format("Variable Value is '{0}'", variableValue), _

            String.Format("Value for Variable '{0}'", variableName))

        Dts.TaskResult = Dts.Results.Success

    End Sub

And (assuming we have a variable named VarTest in our package) when we run the package we'll get something like this:

Pretty simple, and it gets the job done. But there has to be a better way, right? No one wants an annoying modal dialog box popping up during package execution, even when they're developing and debugging. So you may want to do something like this instead:

    Public Sub Main()

        Dim variableName As String = "User::VarTest"

        Dim vars As Variables = Nothing

        Dts.VariableDispenser.LockForRead(variableName)
        Dts.VariableDispenser.GetVariables(vars)

        Dim variableValue As String = CType(vars(variableName).Value, String)

        vars.Unlock()

        Dts.Events.FireInformation(-1, "Test Script", String.Format( _
            "The value for variable {0} is '{1}'", variableName, variableValue), _
            String.Empty, -1, False)

        Dts.TaskResult = Dts.Results.Success

    End Sub

Now, when you run the package you will get something like this:

The output will be written to the Output window in Visual Studio and to any other log providers that you have configured to listen to information messages from the package.

Enjoy!