Your Window Improvements May Vary

I love my new job. This morning I’m sitting in a training session organized by the SQL Server team to bring Microsoft team members up to speed on new Business Intelligence capabilities in SQL Server “Denali”. The session is about to begin, and I’m reviewing the SSIS slides so I can be prepared for questions as they arise. (Did I mention I love my new job?)

I also love ambiguity.[1]

One of the features listed in the presentation is “Variable Window Improvements”. Reading this, all I could think is “we’ve really improved the windows, but they’re different all the time, so we can’t tell you what they are.”[2]

Of course, that’s not what the slide was about. The topic was the specific improvements made to the Variables Window for the SSIS package designer in SQL Server “Denali” Business Intelligence Development Studio. This may sound less than exciting[3] and even in the context of a two-day training event it only rates one slide bullet. But despite this, these tool improvements address a significant real-world need, and are certainly worthy of a blog post or two.

Before we look at the new “Denali” goodness, let’s look at a scenario in pre-“Denali” SSIS today[4]:

1. You’re editing an Execute SQL task in your package, and realize that you need a variable in which to store the results of the query you’re running.
2. You exit the task editor, and in the Variables window you create a new variable.
3. Later on, you’re looking for the variable, and you can’t find it. You frown, scratch your head, and re-create the variable.
4. When you run your package, you find that although the package (and the Execute SQL task) runs successfully, the variable is never populated with the value from the query.
5. You get yourself another coffee and settle down for a long troubleshooting session…

Experienced SSIS developers will probably be saying something along the lines of “well, of course, you needed to click on the package Control Flow design surface between steps 1 and 2. Duh!”

Those who are newer to SSIS may be missing the key point in this scenario: When you create a new variable in SSIS, the variable is created at the scope of whatever container or task was selected in the designer. Although this behavior is consistent and documented, it often comes as a surprise, because nobody actually reads the documentation.[5] It also often comes as a frustration, because most of the time variables should be created at the package level in order to be useful.[6]

In SQL Server “Denali” SSIS, all new variables are created at the package scope. This change ensures that the “what the heck” moments we experienced in previous versions of SSIS will no longer occur, and that all new variables will be defined at the most frequently used scope.

What about those situations when you actually do want a variable at a different scope? In previous versions of SSIS, variable scope could not be changed – you needed to delete the old variable and create a new one at the new scope.[7]

In SQL Server “Denali” SSIS, you can move or copy variables between scopes. Odds are you will need this functionality less than you needed it in earlier versions of SSIS (you know, when it wasn’t there) but when you need it, you’ll love it.

And that’s that. As per usual, I wrote two pages when I could have written two sentences (the ones I highlighted in red) but there you have it. The facts are always more meaningful when presented in the context of a story, and hopefully the story helped turn these two bullets into something more than a”yawn” moment. I’m sure you’ll let me know…

---

[1] In case you’re interested: http://en.wikipedia.org/wiki/Syntactic_ambiguity.

[2] Yes, I crack myself up.

[3] And less funny that my ambiguity joke, at least a little.

[4] This is true with SSIS in SQL Server 2005, 2008 and 2008 R2.

[5] Except you. I know you do, I meant everybody else. You know those guys…

[6] One of the “lightweight best practices” I have included in my SSIS presentations over the years is “always right-click on the package design surface and choose Variables from the context menu” because it ensures that the new variable is at the package scope regardless of what task you were working on beforehand.

[7] This functionality does exist in the amazing BIDS Helper add-in, but it was not included in SSIS.

Back in the SSISaddle

Testing…

Testing…

Is this thing on?

Although I still get the occasional comment on older blog posts, I assume that pretty much nobody subscribes to my blog anymore for SSIS information. When I joined Microsoft Learning in October 2008, the SQL/BI/SSIS side of my life got pushed to the side. I kept working with the tools, but it was no longer a core part of my job, and one of the things I largely gave up on blogging and related technical community efforts. It wasn’t a change I wanted to make, but I needed to ensure that I rocked my new job as much as possible. The end result was that over the last 30 months I’ve posted fewer than ten SSIS-related posts.[1]

This will now change.

This week I joined the SQL Server Integration Services team as a Senior Program Manager.[2]

In the months leading up to the SQL Server “Denali” release, I hope to blog regularly on “Denali”-related features, enhancements and techniques. The “Denali” release is a major release for SSIS, with a ton of exciting new capabilities for new users and seasoned SSIS professionals alike.[3] If you are using SSIS already, SSIS in “Denali” is going to make your life easier in many ways. If you’re just starting off with SSIS, the “Denali” release will ease your learning curve, and help you swear less. In short, I’ll have a lot to blog about.

With this said, it may still be a few weeks before I post again. Not only will I be getting up to speed on my new team and my new role, I will also be relocating my family from New York to Redmond.[4] The relocation will be consuming most or all of my free time for the next few weeks, but once I’m settled in you should expect to hear from me more often.

It’s good to be back.

Back in the SSISaddle indeed.

[1] And that I rocked the new job pretty darned hard, but that’s a longer story. Ask your friendly neighborhood Microsoft Certified Trainer.

[2] I wish you could see the smile on my face as I typed that sentence.

[3] If you want an overview of these new things, please be sure to download and view Matt Masson’s presentation from TechEd North America. We’ll have more announcements coming closer to release, but this session is more than enough to whet your appetite.

[4] I have two children, two cats and one wife, and the effort and stress of moving the whole family is staggering. I thought I knew what I was getting into, but boy was I wrong.