BI Polar

Bring the Special Ops Tour to YOUR City

2011-12-06T15:29:00.001-08:00

Calling all SQL professionals!

Oh yeah, we used that in the last video, didn’t we…

We’re just getting started.

Yes we did, and SQL Server fans from across the country have visited www.specialopstour.com and voted for the cities where they want the tour to visit. As of today the most voted cities are:

San Diego, CA
Boston, MA
Denver, CO
Minneapolis, MN
Chicago, IL
Atlanta, GA
Dallas, TX
Cleveland, OH
New York, NY
Detroit, MI
Phoenix, AZ
Los Angeles, CA

There are only a few more days remaining before the survey closes, so if you want to get your city on the list[1] the time to strike is now. Be sure to cast your vote before the next mission begins…

[1] Or, conversely, to keep your city from being bumped off the list.

5 Tips for a Smooth SSIS Upgrade to SQL Server 2012

2011-11-30T10:06:00.001-08:00

The SSIS team has just released a white paper on upgrading to SSIS in SQL Server 2012:

Summary: Microsoft SQL Server 2012 Integration Services (SSIS) provides significant improvements in both the developer and administration experience. This article provides tips that can help to make the upgrade to Microsoft SQL Server 2012 Integration Services successful. The tips address editing package configurations and specifically connection strings, converting configurations to parameters, converting packages to the project deployment model, updating Execute Package tasks to use project references and parameterizing the PackageName property.

If you’ve been using SSIS in versions prior to 2012[1] please take a moment to check it out. The evenings you save may be your own…

Direct link: http://msdn.microsoft.com/en-us/library/hh667275(d=lightweight).aspx

[1] And if you read this blog, odds are this is a safe bet.

Setting up a SQL Server 2012 RC0 VM

2011-11-29T09:08:00.001-08:00

I ran across this excellent article last week, but forgot to share it before I went offline for the Thanksgiving holiday. Fortunately it was waiting for me in a browser tab this morning to remind me.

How to Build a SQL Server 2012 RC0 Hyper-V Virtual Machine

Setting up a new development[1] environment for a new product is often complex. When the new product is something as diverse as SQL Server 2012, and when you want to explore integration with other products like Visual Studio, SharePoint and Exchange, the setup process can be positively daunting.

In this wiki page, Richard Davis presents a step-by-step approach for building a SQL Server 2012 RC0 VM. I haven’t gone through the whole thing myself, but when I need to build out a comprehensive demo machine, this is where I’ll start.

[1] Or demo, or play, or whatever.

SSIS in RC0

2011-11-22T11:27:00.001-08:00

If you’ve been following the pre-release versions SQL Server 2012 (previously code-named “Denali”) you’ve probably been using the Community Technology Preview (CTP3) that Microsoft released back in July. You probably have already seen the Release Candidate (RC0) that was made available last week.

But what you may not know is how SSIS is changed between CTP3 and RC0. RC0 is a big leap forward for SSIS, and includes some significant new functionality, such as:

Script Component debugging
Attunity Change Data Capture components
Change Data Capture for Oracle
ODBC Source and Destination components
Externalize parameter values in Visual Studio configurations
New REPLACENULL expression function
UI for Pivot and Row Count transforms
Tons of minor fixes and improvements based on CTP feedback

SSIS developer Matt Masson has a more in-depth look at these changes on the SSIS team blog[1], but I wanted to call them out here as well.

The final bullet[2] is also worth stressing. You may not notice these improvements directly, but the SSIS team have made a significant “fit and polish” for RC0, improving functionality that was delivered in (and before) CTP3. If you’ve been using SSIS in CTP3, be sure to download RC0 today and give it a try.

[1] And to give credit where credit is due, the “what’s new” list in this post was blatantly copied from an email from Matt as well.

[2] Which I added to Matt’s list.

SQL Server Special Ops Tour

2011-11-17T09:14:00.001-08:00

If you’ve been following recent SQL Server news, you know that the next version of SQL Server has lost its “Denali” code name[1] and has been officially named SQL Server 2012.[0] This is actually sort of old news, but I’m mentioning it here because it segues so nicely into the main topic of this post: the upcoming Special Ops tour.

The SQL Server Special Ops tour is a 12-city[2] tour being organized by the SQL Server team. You’ve probably seen tours like this before, with technical experts providing deep insight into the features and capabilities of a new software release. But this is a tour with a twist, and the difference is you.[3] To be more specific, it’s members of the SQL Server community who will select the cities for the tour, and who will help select the topics. You can go to www.specialopstour.com today to cast your votes. There will be even more ways to participate, so check out the site today and watch for more news.

And while you’re there, you may also want to check out just what it is that members of the SQL Server team get up to when they’re not at the office…

Do you see any familiar faces?

[0] You also know that SQL Server 2012 Release Candidate 0 is now available for download, as of this morning.

[1] Although I expect most people on the SQL Server team will be calling it “Denali” for many years to come. After joining the SSIS team this summer I had to dredge through my long-term memory to recall what “Yukon” and “Katmai” and “Kilimanjaro” were.

[2] See the clever tie-in there? SQL Server 2012. 12 cities? Ahhh…

[3] Yes, I’m playing this up a little bit. I’m home sick today (which is probably the only reason why I have time to blog during business hours) and am feeling a bit punchy…

SQL Server 2012 RC 0 Available for Download

2011-11-17T08:48:00.001-08:00

If you’re working with the pre-release versions of SQL Server 2012, or if you’re thinking about preparing for its release, today is a big day. The first release candidate has been published on the Microsoft Download Center:

http://www.microsoft.com/download/en/details.aspx?id=28145

RC0 is a major release for SQL Server Integration Services, with lots of improvements since the CTP3 release in July. If you’ve been waiting to kick the tires, you may not want to wait too much longer…

Your Window Improvements May Vary

2011-07-21T11:25:00.001-07:00

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]:

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.
You exit the task editor, and in the Variables window you create a new variable.
Later on, you’re looking for the variable, and you can’t find it. You frown, scratch your head, and re-create the variable.
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.
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

2011-07-20T11:33:00.001-07:00

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.

Transformations are NOT Tasks

2009-10-07T05:55:00.001-07:00

This is another one of those “I thought I blogged this years ago” posts. I was having a conversation with a fellow MCT a few days ago, and he described SSIS data flow transformations as “tasks on the data flow tab.” I didn’t say anything then, but this phrase has been sticking in my mind ever since – not unlike a nasty splinter – and I think I need to get it written down before my brain lets me move on.

Why is this seemingly innocuous phrase causing me so much mental distress?

Because transformations are not tasks!

Let’s list a few characteristics of tasks in SSIS. Tasks:

Are used in the SSIS Control Flow.
Are Containers, with all of the properties (such as the ability to control transaction scope, or to be enabled or disabled) of containers.
Support property expressions.
Support breakpoints and debugging.
Can be individually configured for logging.
Are presented as boxes connected by arrows on the BIDS design surface.

The only characteristic that data flow transformations share with tasks is the last one: they’re presented as boxes, connected by arrows.

But the similarities end there. Transformations are metadata-bound functions that operate on data stored in the memory buffers managed by the data flow engine. They are subcomponents of the Data Flow task, which is just another task. It’s an important task, and it gets its own designer because of this, but it is just another task nonetheless.

Why is this important to me? Some (ok, most) may argue that I’m just being pedantic. And perhaps they’re right, to some degree. But any technical field has its own terminology that allows its practitioners to communicate efficiently and precisely. Hearing an SSIS developer use “task” for “transformation” is kind of like hearing a surgeon use “abdomen” for “stomach” or a .NET developer use “component” for “assembly.” The terms are related, but their meanings are very different. In each case it’s probably likely that the speaker knows the difference and is simply not speaking carefully, but sometimes (particularly with the surgeon ;-) you just want to be sure.

So the next time someone tells you that he’s using “the Lookup Task” please send him here.

P.S. I should also be sure to mention that the MCT who used this offending phrase probably does not need this rant to let him know what’s going on. MCTs tend to have a solid grasp of the technologies they teach; you tend to crash in burn in front of the classroom if this is not the case. But I knew that I was going to have this stuck in my mind forever if I didn’t say something somewhere…

SQL Server MVP Deep Dives

2009-09-29T10:18:00.001-07:00

I’m not a SQL Server Most Valuable Professional anymore[1], but I was when this amazing project got underway, and I am proud to contribute as an author on SQL Server Integration Services. SQL Server MVP Deep Dives is a unique book. It has:

53 authors
59 chapters
Coverage of database design, development, administration and performance tuning
Coverage of the SQL Server BI suite: SSIS, SSAS and SSRS

But that’s not the coolest thing about this book. The coolest thing is where the money goes. All author royalties (that’s right – 100%) go to War Child International, a network of independent organizations, working across the world to help children affected by war.[2] So not only will this book help you become a better SQL Server practitioner, it will also help children around the world.

You can pre-order the book online today at the SQL Server MVP Deep Dives web site, and get immediate access to a pre-release PDF copy of the book. (Note: if you order before the end of September and use the code pop0928 at the checkout you will also save 50%!) Also, if you’re attending the PASS Summit in Seattle in early November, you can buy a copy at the conference bookstore and it signed by many of the authors. I’ll be there, and based on the email I’ve seen we’ll see a majority of the authors and editors should be there as well.

If you only buy one SQL Server book this year, why not make it this book? And since all proceeds go to benefit a great cause, why not buy two?

I’ll see you at PASS!

[1] You can’t be an MVP and a Microsoft employee at the same time, so when I joined Microsoft they took away my MVP status. Which was OK.

[2] Not, as some may think, a non-profit organization dedicated to helping aging Jethro Tull fans.

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

2009-09-21T15:49:00.001-07:00

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.

2009-05-22T11:00:00.001-07:00

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? ;-)

Spatial Demo Goodness

2009-05-19T11:25:00.001-07:00

If you’re someone who presents on SQL Server topics, you have probably run into something of a wall when it comes to getting interesting spatial data sets to demonstrate. The spatial data included with the SQL Server 2008 sample databases is functional, but not particularly complex or interesting. There is free spatial data available for many different sources online, but it tends to be difficult to find, in different formats, and annoyingly difficult to load into SQL Server.[1] And of course, the not-free spatial content out there tends to be really, really not-free, and while it may make sense to pay a premium price if you are developing premium software, but for demo purposes this is generally a non-starter.

Enter GeoNames.

GeoNames is an open source provider of spatial data. Essentially they have many disparate sources of free spatial content and have aggregated them into a single location, with many different access methods. They support web service access (and publish a nice set of client libraries too) which is nice for direct application integration, but to me the cool factor comes from the ability to download text dumps of the whole database or just the countries you want. Because then you can load the data into SQL Server 2008 and let the demo goodness begin.

And Ed Katibah, PM for the SQL Server spatial team, has posted instructions for loading GeoNames data into SQL Server 2008. It’s great to have these steps documented because there are quite a few of them, but hopefully you’ll only need to perform them once.

So if you have been waiting for great spatial data that’s available for free, wait no longer.

I should also point out that I became aware of this cool resource not based on my own hard work and research, but instead because of the excellent Simple Talk newsletter that Red Gate Software produces. And I should probably mention that the primary reason I blogged about it is that my friend and colleague, senior SQL Server trainer and all-around good guy Chris Randall has been working on building better spatial demo sets, and I’ve heard rumors that he occasionally reads this blog. Hopefully this will save him (and you, and me) some work…

[1] Please keep in mind that I don’t claim to be a SQL Server spatial expert, so what is “annoyingly difficult” for me may be “exceptionally simple” for someone with more experience, but it likely to be “annoyingly difficult” for many people.

More on Package Configurations

2009-04-09T09:20:00.001-07:00

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

2009-04-05T14:57:00.001-07:00

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:

I dock the Variables window in the upper left corner in its own tab group.
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.)
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.
I dock the Solution Explorer window in the upper right corner.
I dock the Team Explorer window in the same tab group as Solution Explorer.
I dock the Properties window in the lower right corner in its own tab group.
I dock the Error List window on the bottom.
I dock the Output window on the bottom in the same tab group.
I dock the Pending Changes window on the bottom in the same tab group as the Error List and the Output windows.
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 and the Package ProtectionLevel Property

2009-04-05T13:55:00.001-07:00

This is another one of those “I could have sworn I blogged on this years ago” posts.[1] I was looking for something I’d already written that discussed the ProtectionLevel property of the SSIS Package, but search as I might I could not find it. So I guess it’s time to write it, eh?

(As a note for the lazy reader, the moral of the story is that you should use the DontSaveSensitive ProtectionLevel for your packages, and use package configurations to apply the sensitive property values to your packages. Feel free to read the next five pages, but it all boils down to this.)

First of all, let’s start off by reviewing the property itself. Take a look at the properties of any SSIS package and you’ll see it there:

Notice the description there at the bottom of the Properties window? “Specifies the protection mode and the protection method of the package.” Hey – that’s pretty self-explanatory, so maybe we don’t need a blog post after all. Right?

Maybe not.

Before we continue, why not take a look at what SQL Server Books Online (BOL) has to say about this property?

Ok, so you’ve read the docs. Now let’s review:

Component developers get to specify which properties of their components are considered sensitive.
For the built-in components, this includes the password property of connection managers, some system variables and the XML nodes in the underlying DTSX file that store this information.
SSIS users (that means us, the package developers) cannot control what is considered sensitive.

Why does this matter?

Well, SSIS is designed to be secure. One aspect of this is that any “sensitive” information that is part of your package (think about it – many packages include information like passwords in connection strings for connecting to databases and legacy systems where valuable data is stored) needs to be protected. And by protected, we mean never, ever stored in clear text.

That’s right, donkey – never, ever.[3]

But wait, you say – I’ve built many SSIS packages and I’ve never been prompted to encrypt any sensitive anything. How am I supposed to believe you?

Good question. Like most developers[4], the fine members of the SSIS product group wanted to avoid irritating their users.[5] Because of this, they wanted to make protecting sensitive data seamless, even transparent. So they did. Take another look at that screen shot of the Properties window above. Do you notice the EncryptSensitiveWithUserKey option? That’s the default.

This means that, by default, SSIS uses the Data Protection API (DPAPI) to encrypt any sensitive data with the user key of the user who saves the package. Every Windows user has a user key, so this is pretty straightforward. Then, when the user goes to open the package again, the encrypted data is automatically decrypted without the user needing to do anything. Pretty slick, eh?

Yep. Pretty slick. Especially for SSIS developers who live in a world where they never work in teams and never have their packages run in an unattended batch job that uses a service account.

Unfortunately, I don’t live in that world, and neither do many of the SSIS developers I know.

Ok, let’s be fair. That sounded awfully critical, but it wasn’t intended to be. I generally don’t like to criticize unless I can suggest a better alternative, and in this context I don’t see a more reasonable default behavior. It just works, and it lets developers be productive with SSIS without needing to worry about manually configuring security settings.

The criticism that I have is that people just don’t seem to understand this, and that causes problems. If one developer didn’t understand it, then it’s probably his fault. But if dozens or hundreds of people have this same problem (and they do – just take a look at the SSIS forums on MSDN to see how often questions related to the ProtectionLevel property come up) then the communication about the feature hasn’t been what it could be.

So what is the problem, and how does it show up? Well, consider one of these situations:

The package developer checks his package into source control[6] and another developer checks it out to make changes. The second developer can open the package, but gets an error saying “Failed to decrypt protected XML node "DTS:Password"” and the package will not execute.
The package developer deploys the package to the production server and the DBA schedules it to execute via a SQL Server Agent job. When the job is run, the job step fails with an error saying “Failed to decrypt protected XML node "DTS:Password"” and the package will not execute.

I won’t go on. You probably see the trend already. With the default settings, initial development is easy, but downstream tasks are more difficult. Only the user who saves the package can edit and execute it without error. This is probably not what we want.

What about the other options? Let’s consider:

EncryptAllWithUserKey – This option has all of the drawbacks of the default EncryptSensitiveuserKey option, but has the additional drawback of encrypting the whole darned package, so not only can no other user execute the package without re-entering the sensitive data, no other user can even open the package. Probably not what you want.
EncryptSensitiveWithPassword – This option replies on the package developer entering a password for the package, and then entering that password every time the package is opened or executed. Um… right. I don’t know about you, but my real-world SSIS projects tend to involve many dozens of packages. And I typically have a dozen or more open in Visual Studio at any given time. So picture me staggering to my desk on a blurry Monday morning, desperately clutching my mug of coffee, and launching Visual Studio. Visual Studio loves me, so it has remembered the packages I had open when I shut down for the weekend, and in a display of digital affection it re-opens them all.[7] Now picture the look on my face as I am prompted to enter the &^%$%@(* package $#^%#& password for every &@%$(^@ package as it opens. (And for that matter, picture the look on the faces of the poor people from accounting down the hall as they run for the fire escape.) And to make matters worse, this option pretty much negates the security it’s designed to provide, because every developer on the team needs to know the package password, and at least one developer[8] will put that password on his white board or on a Post-It Note.
EncryptAllWithPassword – Just when you thought it was safe to go back into the Properties window, here comes an option which combines the worst of all worlds. Not only do you have to enter the &%#*& password every time you open the package, the whole package contents are also encrypted so if you forget the password[9] you’re pretty much SOL[10].

So what does that leave us? That leaves us with two options. The first is the one that I use and advocate: the DontSaveSensitive option.

This option, like the others we’ve seen so far, is pretty much self-describing. It protects the sensitive data by simply not saving it in the package file. What’s not to like? No password, no user keys, everyone can open the file without error. It’s a little slice of perfection in our imperfect world!

Maybe.

Maybe not.

The difficulty, of course, comes in when you actually want to execute the package. The package can’t run if it doesn’t have a valid connection string to connect to that legacy database server from which it extracts data, now can it? So where does the sensitive data get stored, if not in the package? The answer is that we, the package developer, need to store that sensitive data in package configurations. This could be XML config files, it could be a SQL Server database, or the Windows Registry – there are many options. And each one of them can be secured in its own way, through database permissions, NTFS permissions or Registry ACLs.

The problem that some developers have with this is that it doesn’t let them get down to doing what they want to do the most, which is build their packages. And what do we say to those developers?

We say “tough luck!”

We don’t have much sympathy for those developers, because they’re missing the big picture. Yes, it can slow things down up front to have to add configurations to your packages, but this saves a ton of time later on. Configurations are a required feature of packages that will need to get deployed to multiple environments (and unless your packages will only ever be run by you on your machine, then this means your packages) and by adding them right at the beginning of package development you will have a much greater chance of having a successful deployment than if you wait until you start deploying to start thinking about deployment.

So this is what I recommend that SSIS developers do when building their packages:

Set the ProtectionLevel property to DontSaveSensitive
Add a package configuration for each connection string or other sensitive property in the package.
Test everything and make sure that it works.
Save the package and use it as a template package moving forward.[11]

Ahhh… life is good. Life is so much simpler when you have prescriptive guidance.

But wait… What about that last option. What about ServerStorage? Should we ever use that one?

ServerStorage can only be used when deploying packages into a SQL Server database[12]. If you deploy to SQL, then you can rely on the database to protect your sensitive data. But as you probably know, this doesn’t apply to package development. Visual Studio can only work with DTSX files on the file system – it cannot open packages from or save packages to SQL Server. This means that while ServerStorage is a viable option for once package development is complete, but not during development. During development you want to use DontSaveSensitive, even if you are planning to deploy to SQL.

So there you have it. I’ve spent five pages[13] saying “use the DontSaveSensitive package ProtectionLevel option when you develop SSIS packages.” But even though it took longer to read, hopefully you’ll have a better idea of why you should choose this option than if I’d just come out and told you…

[1] Just as a side note, re-read the subject line. Doesn’t it sound like the name of a children’s story? It was all that I could do to not write this post as an AeSISSop’s Fable or modern cautionary tale[2].

[2] I also started putting it to the tune of the Gilligan’s Island theme before i caught myself. I haven’t been feeling well lately, you see…

[3] Say it in your best Shrek voice and it may make sense. Especially in the fable context.

[4] Read the rest of the sentence and then have a good laugh with me, ok?

[5] Wait, then wipe the tears from your eyes, and then start reading again.

[6] And we all use source control, right?

[7] Awww….

[8] You know who I’m talking about.

[9] And can’t find your way down the hall to Bob’s white board.

[10] That’s “Server Offline” to you.

[11] There will likely be other things that need to be set up in your template package too, but this is a good start.

[12] I personally prefer file system deployments and so do most of the SSIS professionals I know. Check out this post from Jamie Thompson where he discusses this topic, and make sure you also click on the link in the top of his post too.

[13] And if memory serves me, set a new record for the number of footnotes in a single blog post.

Hey, That Was Fun!

2009-03-30T08:57:00.001-07:00

As you probably know, I’m a Microsoft Certified Trainer (MCT). As you may not know, MCTs are required to renew their certification each year, and the 2009 renewal period is currently underway. I got my MT 2009 welcome kit a week or so ago.

And with that welcome kit came a nice surprise – a voucher for one free Microsoft Certified Application Specialist (MCAS) exam.

Now, if you’re like me, you probably like to think of yourself as being a geek. Someone who builds software that has never existed before, and who dominates the poor software that is unlucky enough to cross your path. (Or something like that anyway.) So although I had been aware of the MCAS certification for some time, I had never really considered trying to achieve it. But hey – free is free, right?

So last Friday I scheduled my exam (77-601: Using Microsoft Office Word 2007) for this morning. I was going to be out of the office for a dentist appointment anyway, and the test center is on the way home, so why not, right?

Well, to make a long story short, I passed the exam and had a great time doing it. The exam was delivered in Word 2007 – each question gave me a list of things to accomplish and I then used Word 2007 to achieve the required goals. It didn’t matter what steps you used (I’m a big keyboard shortcut user and don’t use the Ribbon too often) so long as you achieve the requirements as written.

The thing that struck me here was that this exam really tested my ability to use Word 2007 as a “power user.” Not just someone who typed documents, but someone who knows what features are there and who knows how to use them. I doubt that I’ll stop taking my technical certification exams any time soon, but I think that I may just need to try my hand at the Excel 2007 and PowerPoint 2007 exams too…

SSIS Development and Source Code Control

2009-03-28T19:16:00.001-07:00

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:

Open an SSIS package in Visual Studio – a package that has at least one user variable defined.
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.
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.
In the Variables window, click on the name of a variable. Don’t change anything, just click.
Then click on the design surface. Again, don’t change anything – just click.
Then look up at the tab. What do you see?
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.

Going to Tech·Ed 2009 North America!

2009-03-26T07:39:00.001-07:00

I’ve been to Tech·Ed North America every year for the last four years. It’s Microsoft’s biggest technical conference and is an amazing opportunity to learn and to network – and it’s always a ton of fun as well. People have been asking me a lot lately “are you going to be in Los Angeles?” and I haven’t been able to answer them.

Until today.

I learned this week that I will indeed be going to Tech·Ed. I’ll be In Los Angeles the whole week, from May 11 through May 16, helping to represent Microsoft Learning at the conference. I’ll have more details to share about what I’ll be doing as the conference approaches, but for now I just think that I’ll be staying away from places where I see this sign:

Hang here? Yikes! ;-)

Seriously, interacting with Microsoft Certified Trainers is probably going to be a big part of my role at the conference. I just wonder how “hang” ever got through the layers of review.

So I’ll be there – will you?

Get on the Bus!

2009-03-24T04:08:00.001-07:00

if (you.Location.Match(“Atlanta”,
    “Charlotte”, “Philadelphia”, “Columbus”,
    “Indianapolis”, “St. Louis”, “Kansas City”,
    “Denver”, “Albuquerque”, “Phoenix”,
    “Las Vegas”, “Los Angeles”) ==
        MatchResults.CloseEnough)
{
    you.EnterContest(“Get on the Bus”);
}

if (you.WinContest(“Get on the Bus”))
{
you.AttendConference(Microsoft.Conference.TechEd,
Cost.Free);
}

Sound like a good deal? Learn more and enter here: http://www.microsoft.com/learning/mcp/offers/getonthebus/default.mspx

(I just wish that I were eligible…)

Indexes and SSIS Data Flow Performance

2009-03-13T07:38:00.001-07:00

Wow – it’s been a while, hasn’t it?

As you may know, I took a job with Microsoft back in October. It’s been a fun (and insanely busy) five months; the work is challenging and diverse, the team I work with is talented and motivated and there is always something new and interesting to learn and/or do. What’s not to like?

In fact, even though BI isn’t a core component of my job role, I have been working with SSIS and the rest of the SQL Server 2008 BI tools to get easier access to some of the data I need to make better decisions and to support the recommendations I make with some “real numbers.”[1] It’s an easy way to add some value to existing data, and for me it’s a fun way to keep my BI skills from getting too rusty.

So… I re-learned an interesting lesson the other day: Don’t load data into indexed tables.

One of the SSIS packages I’ve built extracts data from a set of text files and loads it into a set of SQL Server tables.[2] There is around 20 GB worth of text file data that ends up in one particular table. When I ran this package Monday evening, I found it was still running Tuesday morning, which was not expected. I looked around in SSMS and noticed that there was one non-clustered index on this table. I had added it recently and had not dropped or disabled it before running the package. So when the package finished running, I took a look at the execution results and found this:

DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 6:03:50 PM
Finished: 9:54:20 AM
Elapsed: 57028.7 seconds

That’s right – the package took nearly 16 hours to run. Ugh!

So I re-ran the package this morning, after having ensured that all indexes on this table had been dropped. And the result?

DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 9:49:21 AM
Finished: 10:41:15 AM
Elapsed: 3114.05 seconds

That’s right – the package completed in well under an hour, taking roughly 5.5% of the time it took to execute the package against the indexed table.

But wait, you say – that’s not a fair comparison. You still need to rebuild that index!

You’re right, of course – the time to rebuild the index needs to be considered before we can make any apples-to-apples comparison. So what I did was to re-create all of the indexes on this table (four indexes, each with included columns, for a total index size of 15.8 GB) which took an hour and 39 minutes. Even though this is longer than the package execution itself, the total end-to-end execution time is still only around 15.9% of the package execution time with one index on the target table. In the end we achieved an 84% reduction in execution time.

So what’s the lesson?

Drop or disable your indexes before loading data into your tables, and then re-create or enable them once you’re done. If you don’t, then SQL Server will keep incrementally rebuilding the indexes while your package executes, thus killing your performance.

This isn’t new advice – this has been a best practice for bulk loading data into SQL Server for as long as I can remember. But it is something that I have not blogged on before, and since failing to follow this advice bit me this week I thought I would take the time to share my pain with the world in hopes that someone else can avoid it.

And, of course, it’s good to blog on SSIS again. Hopefully I’ll be posting again before another few months have passed…

[1] Please note that I’m not calling this a “BI solution” here. At this point I am more in a discovery phase than anything else. The components I’ve developed, while functional, will probably get thrown away once they have served their purpose as prototypes and learning tools. All of the performance numbers come from running the packages on a laptop, with both the target data database’s data file and the source text files on the same external 5,400 RPM USB hard drive. No, I wouldn’t do this in production either. ;-)

[2] The astute reader will note that this is in flagrant violation of the “each package should only load data into one table” best practice that I talk about whenever I have someone who will listen. The scenario in which I’m working here provides an interesting exception to this guideline, and please trust that I did think hard before going against my own advice.

I’m Certifiable – Are You?

2009-02-06T09:50:00.001-08:00

Odd are, if you read this blog you are certifiable. One way or the other.

But of course, I’m talking about Microsoft technical certifications in the developer and IT Professional world. (What were you thinking?) I’ve been a Microsoft Certified Trainer since 1996, and have taken any number of certification exams since then. I love certification for many reasons[1] - and one of the reasons is that I thing that the certification exams are fun.[2] I love the ability to demonstrate the depth and breadth of my knowledge in an almost competitive setting – it’s almost like playing chess with myself.

But not everyone has seen the fun side of certification. Until now!

Microsoft Learning[3] has just launched a new online game that is like a cross between a certification practice exam and an Xbox 360 game show. It’s a riot – I played a quick game over lunch today and loved it.

But instead of reading my opinions, you should check it out here. Enjoy!

[1] But I’m not going to try to go into all of the reasons why. If you want more on that front, check out the Born to Learn blog where Ken Rosen and his team post just about every day.

[2] Please refer to the title of this post again.

[3] Yes, this is the group within Microsoft where I work these days, even though I can’t take any credit for the genius behind this site.

Loving Windows 7

2009-01-16T09:05:00.001-08:00

I installed the public Windows 7 beta on one of my laptops early this week. This is a laptop that I use all the time when I’m at home (I love the keyboard, so I sit at this one and Remote Desktop into my other machines) but it’s been running Windows XP because Windows Vista never ran particularly well on its 2004-era hardware. And as is always the case when working with a pre-release operating system, there is both good news and bad news.

Let’s do the bad news first:

I’m traveling this week and next, so I only had two days to play with new features before heading out on the road.
I have a bunch of demos I need to do next week at the MCT Summit in Redmond, so I can’t blow away the Windows Server 2008 install on my demo laptop and replace it with Windows 7 until the Summit is over.
That’s about it for the bad news…

The good news is much longer, so I’ll just hit on the highlights:

Windows 7 runs faster and better than Windows XP ever did, even with the same software running. I’m vastly impressed by how well it runs, and I can’t wait to get it installed on more modern hardware.
It looks and feels great, with all of the nice features of Windows Vista (I love Vista search and some of the Explorer enhancements that I miss so much when I go back to Windows XP or 2003) and is more usable (and just plain prettier) as well.
Windows 7 automatically detected my Nvidia video card and downloaded drivers for it. This may sound trivial (and isn’t really something for which i can give credit to the Windows 7 team) but the new driver finally works. For all previous operating systems and driver versions, this video card has always displayed any video being played in full-screen mode on the secondary monitor. This meant that I could not work on one display while having a video (any video – not just Manowar videos) play on the other. With the new driver, this is no longer the case, and video finally works the way that it should.
Did I mention it’s amazingly fast? ;-)

But the thing that prompted me to post this article is not my own experience. I found today a list of “Windows 7 Secrets” published by Tim Sneath here: http://blogs.msdn.com/tims/archive/2009/01/12/the-bumper-list-of-windows-7-secrets.aspx. This includes a ton of keyboard shortcuts (one of my favorite tools) and ways to work with the new OS features that you’d be unlikely to stumble across on your own. Definitely check it out the next time you sit down at your Windows 7 machine, or if you’re wondering whether you should make the move…

Christmas Came Late!

2008-12-31T11:12:00.001-08:00

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...

Math and Terror

2008-12-23T06:27:00.001-08:00

My son just celebrated his eighth birthday. While he was eating his birthday breakfast before heading to school, he came out with this gem:

"I'm half of sixteen today!"

Now part of me is happy that he's thinking of mathematical relationships at the breakfast table. But another part of me is thinking how I am so incredibly not yet ready for sixteen. These first eight years have gone by so quickly, I can't begin to imagine how quickly the next will fly.

So the next time you're wondering if the glass is half empty or half full, just be glad that it isn't half of sixteen...

Better File Rename

2008-12-18T09:59:00.001-08:00

This is just a random tool recommendation - I've been meaning to post it for months, but have never made the time. Well, a meeting just finished early, so the time made itself.

I've love this tool: Better File Rename

It's a Windows shell extension that gives you a ton (maybe more) of batch file renaming options. Right-click on a folder or a group of files and you can:

Add characters to the beginning of a file
Change the file extension
Add sequence numbers to the file name
Add date/time stamps to the file name
Replace characters or string patterns in the file name
A staggering number of other possibilities

I've been using this delightful little tool since 2002, and it works great on Windows XP, Windows Vista, Windows Server 2003 and I assume Windows Server 2008 as well, although I've never personally tested it. If I ever rebuild my primary laptop to run Server 2008 then I'll know, because this app is one of the few 3rd party "must have" utilities that I install whenever I rebuild my main machine.

Ok, that's that. Check it out - download a trial version and see if it rocks your world too. Especially with all of the Christmas photos you'll soon be taking, you know you want it...

Changes in SQL Server 2008

2008-12-15T07:28:00.001-08:00

MCT Russ Loski recently shared this great link with the SQL Server trainer community:

http://msdn.microsoft.com/en-us/library/cc280407.aspx

This is the top-level "Backward Compatibility" topic from SQL Server 2008 Books Online, and includes sub-topics for the DRBMS, SSAS, SSIS, SSRS and replication, covering deprecated features, behavioral changes, breaking changes and more.

If you're looking into what's new and different (and what might bite you if you're not careful) with SQL Server 2008, this is a great place to start. Thanks, Russ!

"Bonus" Technical Deep Dive Session at the MCT Summit - Redmond and Prague

2008-12-10T13:40:00.001-08:00

The vast majority of the content at the 2009 MCT Summit events is heavily weighted toward the IT Professional audience. This is largely due to the fact that the major developer and database product releases took place in 2007 and 2008, while there are significant releases in-flight for Windows and Exchange Server. But I believe there should still be some deep technical content for at least one underrepresented trainer audience: the BI developer.

So I'm going to fill in this gap by presenting an "off the schedule" technical deep dive on SQL Server Integration Services. I did something similar at the 2008 Redmond summit and it was very well received, so I'm going to model the 2009 Summit session on the same model. Here's the deal:

“Everything You Ever Wanted to Know About SQL Server Integration Services but Were Afraid Your Students Would Ask”

In this technical “deep dive” session, Matthew Roche will take attendees on a wild and sometimes horrifying ride into the dark underbelly of real world SSIS development that existing SSIS books and courseware doesn’t effectively cover, including development and deployment best practices, data flow internals and performance tuning and more. But be warned – there will be no fixed agenda for this session! The topics covered will be driven by attendee involvement, so the more questions you bring to the session the more everyone will get out of it. If you teach (or fear you may be asked to teach) the SSIS courses for SQL Server 2005 or SQL Server 2008, this is a session you don’t dare to miss.

Does this sound interesting to you?

If you are attending either event (Prague or Redmond) and would be willing to attend this session after the summit sessions end one day[1] then please reply here[2] to express your interest. If anyone (even one attendee) is interested then I will come prepared to spend as much time as necessary (I think we ran around three hours at the 2008 Summit in Redmond) to give each attendee everything he needs. So speak now or forever hold your data...

[1] This is a key point, as it will mean skipping on some other planned after-hours event, I'm sure.

[2] Or in the microsoft.private.mct.mctsummits newsgroup.

Connection Strings

2008-12-05T08:50:00.001-08:00

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.

ThinkPad Keyboard Madness

2008-12-04T20:30:00.001-08:00

I own lots of laptops. After being an independent software development and BI consultant for many years, I've assembled quite the collection. I have Dells and Toshibas[1] and have had Gateways and HPs as well. My little home office is filled with them[2].

I also have a Lenovo. It's a ThinkPad X300, and if I never needed to type on it, it would probably be one of my favorite computers. It's light, it's powerful, it's solid, and just about everything you could want a laptop to be.

But I do have to type on it. And because of this, I swear. A lot. In fact, I swear so much I just may need a new chart.

Why all the swearing, you ask? Well, before I answer, please take a look at your keyboard, down in the bottom left corner. Go on. I'll wait.

There.

You see, if you're typing on a ThinkPad, you'll probably[3] notice that the bottom left key is the control "Ctrl" key, and to the right of that (if you're using a laptop anyway) is the function "Fn" key.

Still with me? Good.

Now if you were horribly evil in a past life and are currently being punished by having to type on a ThinkPad, you'll instead notice that the Fn and Ctrl keys have been reversed. Swapped. Inverted.

And this is insane!

Ok, you ask - what's the big deal? Who cares?

First and foremost - me. I care a lot. You see, I'm a developer. And while I don't buy in with the whole developer stereotype[4] I definitely am a very keyboard-centric computer user. I prefer to use my keyboard whenever possible, and there are literally thousands of times each day when I perform some task that utilizes a Ctrl+Key keyboard shortcut. For example, you may be familiar with some of these well-loved productivity enhancers:

Ctrl-C = Copy
Ctrl-V = Paste
Ctrl-Arrow (Left or right) = Jump one word in text
Ctrl-Shift-Arrow (Left or right) = Jump and highlight one word in text
Ctrl-F4 = Close current tab or child window

I could go on and on and on. A significant portion of the most common Windows keyboard shortcuts involve the Ctrl key.

Now at this point you're probably asking "But Matthew, since this incarnation of ultimate evil that Lenovo uses for a keyboard inverts the Ctrl and Fn keys, what do these keyboard shortcuts do if your finger, trained through decades of computer use, hits the Fn key instead?"

Funny you should ask. This is what happens:

Fn-C = Nothing
Fn-V = Nada
Fn-Arrow (Left or right) = Zip
Fn-Shift-Arrow (Left or right) = Zilch
Fn-F4 = Puts the computer to sleep!!!!!

That's right. If you try to close out a child window and let your fingers follow the paths that are ingrained deep in their muscle memory, you have just effectively closed the lid on your accursed laptop and have shut it down. Even if you're in the middle of working. Even if you're in the middle of a giant download that then needs to be restarted. Even if you're connected remotely to the corporate network and you then need to dig out your smart card so you can re-connect. Even if you're hosting a LiveMeeting that your boss' boss' boss is attending. Yeah. Even then.

I could go on. I could rant about how there is no way to re-map these keys, because the Fn key isn't exposed to the standard Windows keyboard APIs. I could rave about how the Esc key is on its own row above the function keys (F1 through F12) so that when I try to hit F2 to rename a file I get F3 (search) and when I attempt to hit Esc I get F1 (help) instead. I could blow my top and go on and on about how when I use my other laptops (which outnumber my lone ThinkPad 7:1 at this point) the lessons that my poor fingers are being forced to learn are tripping me up when I use normal keyboards. I could go on and on and on...

But I won't. Because it's far too late for a rant like this to go on any further. Instead, I'm going to fill out a little survey that Lenovo has put together. And I encourage you to do the same. Please, please, please do the same.

I think it's great that Lenovo is conducting such a survey. But I think it is very frustrating that they're just getting around to asking. Take a few minutes and Live Search around on "ThinkPad" and "keyboard" and you'll see what I mean - dozens of other frustrated ThinkPad owners have poured their anguish into the ether as well. (My favorite one is here.) And even more frustrating, the survey doesn't even mention the Fn and Ctrl key positions - it focuses on the Esc, Home, End, PgUp and PgDn keys instead. Ugh...

But hopefully they're listening now. This is what I told them at the end of my survey, in response to the question "Of the keys that vary between different key layouts, for which keys are you most concerned about their location (e.g. Esc, Home, End, PgUp, PgDn, others, etc.)?":

"The Ctrl key MUST be in the bottom-left corner, immediately below the Shift key. If this is not the case in the hardware then there MUST be a way to re-map these keys in the OS or BIOS. Yes, you need to keep your historic users happy, but you also need to understand the basic mechanics of using keyboard shortcuts and the PAIN that users feel when attempting to adjust to the ThinkPad's non-standard layout. Until one of these solutions is implemented I will never buy or use another ThinkPad."

And I won't. It pains me that much. There are lots of small, light, powerful laptops out there just waiting for my hardware budget, and only the ThinkPad comes with a keyboard that makes my typing life miserable every day. It's a show-stopper for me, and I will never buy another laptop without looking at the keyboard first.

But now it's time for me to sleep. And hopefully to dream of non-ThinkPad keyboards...

[1] Which is another story, but probably not one I'll rant about just yet...

[2] And, in the winter, heated by them.

[3] I saw "probably" simply because I have not owned one of every type of computer ever made, but for my own personal sample "probably" means 100% of the time.

[4] For example, I dislike Red Bull, don't drink Mountain Dew, and I'm tall, good looking[5] and very comfortable around members of the opposite sex. ;-)

[5] Humble, too.

Know Your Data... Werewolves

2008-12-04T14:51:00.001-08:00

(That's right. Werewolves.)

Over a year ago, I posted a list of SSIS best practices, and I've presented on this topic a dozen or more times since then at conferences and user groups and seminars. One of these best practices was "Really know your data – really!" and the advice went something like this:

"If there is one lesson I've learned it is that source systems never behave the way you expect them to and behave as documented even less frequently. Question everything, and then test to validate the answers you retrieve. You need to understand not only the static nature of the data - what is stored where - but also the dynamic nature of the data - how it changes when it changes, and what processes initiate those changes, and when, and how, and why. Odds are you will never understand a complex source system well enough, so make sure you are very friendly (may I recommend including a line item for chocolate and/or alcohol in your project budget?) with the business domain experts for the systems from which you will be extracting data. Really."

Well, last night I stumbled across some old meeting notes that reminded me of why this is so very important. The notes were incredibly brief, and consisted largely of a quote from the client project owner. But before I share the quote, please let me share the context and story[1]...

I was helping to design and build a BI application that my client was then re-selling as a service to their customers.[2] We were loading in data from a variety of source systems that provided the same type of data, but which were implemented by different vendors. One of the systems, called FooBar[3] was consistently causing us problems. The data import was running without error, and everything looked good on the surface, but the FooBar-using customers were unhappy[4] because the data they were seeing through the BI portal was incorrect.

Yuck.

The client project owner had, years earlier, worked with the development group that built FooBar, and with his insight we were able to discover that occasionally the data export process in FooBar was failing silently, so that we were getting only partial exports, and there was no way to tell - no error was raised, no checksum existed, and because of some nasty internal details (which I won't go into here) the effort involved in updating FooBar would be very significant. It was not a good time.

So what's the quote? It was this:

"You guys planned for wolves. FooBar is werewolves."

Even after all this time, I still remember the deadpan delivery for this gem. And it was so true. We'd gone in thinking we were prepared for all of the usual problems - and we were. But we weren't prepared for the horrifying reality of the data problems that were lying in wait. We weren't prepared for werewolves.

So what's the moral of the story? Plan for werewolves. Assume the worst. Test early and often, and test failure scenarios, not just happy-day scenarios. Because the time to learn that there really are werewolves is when there is still time to pack a crossbow and some silver bolts, not when the full moon is rising.

Trust me.

[1] Names have been changed to protect the innocent. And the other people too...

[2] This has been a recurring theme for me, so it's probably not the company you're thinking of.

[3] I changed the names, remember?

[4] I may also use a literary tool called "understatement" in this story...

Choosing the "Right" SSIS Configuration Location

2008-12-04T09:07:00.001-08:00

One of the questions I get asked quite often boils down to this:

"Should I store my SSIS configuration data in SQL Server or in XML configuration files?"

And my every-so-helpful answer usually is:

"Yes!"[1]

That's right - very accurate, but not very useful.

Of course, for this question isn't quite as simple as it might seem at first glance, and there really is no single answer that is both generally accurate (like mine is) and useful (which mine is not) at the same time. The truly useful answer is more complex, because it relies on the context of the question more than it relies on the question itself. There is no "best" or "correct" or "right" choice for configuration storage in general - it all depends on the packages, the project and the project team.

What does this mean? I like to summarize it something like this:

If you are using a file system deployment, it probably makes more sense to use XML configuration files.
If you are using a SQL Server deployment, it probably makes more sense to use SQL Server configurations.
If your ETL solution is managed by the application owner or server administrator, it probably makes more sense to use XML configuration files.
If your ETL solution is managed by the database administrator, it probably makes more sense to use SQL Server configurations.
If project team members and/or administrators have past experience and success with a given configuration type, it probably makes sense to use that type unless there is some compelling project-specific reason to do otherwise.

Notice that there's nothing in this list about the capabilities of the different configuration types. I've never found a problem that could be solved with one configuration type that could not also be solved with the other.

Of course, there are differences in the approaches that you use when selecting and implementing a configuration strategy for an SSIS application. And it's not always obvious how to get started, or where to go one you do get started. Fortunately, there are quite a few resources available to help make the right decision for you and to implement your configuration strategy correctly the first time. In particular, there is a small set of MSDN community articles[2] written by SSIS-focused SQL Server MVPs about SSIS configurations. Take a look at these:

Best Practices for Integration Services Configurations by Jamie Thomson
Defining a Configuration Approach for Integration Services Packages by John Welch
Understanding Integration Services Package Configurations by Rafael Salas
Reusing Connections with Data Sources and Configurations by Matthew Roche

The articles are listed in the order of "most likely valuable" so if you don't have time to read all four, you should read the top ones first.

Check it out, and good luck!

[1] I blame my mother for this. My childhood was filled with conversations that went like this: "Are we having chicken for dinner, or fish?" "Yes!" "Um..." ;-)

[2] There is an interesting story behind these articles. Shortly before SQL Server 2008 shipped, the SSIS team asked a bunch of MVPs if they would like to write SSIS articles for MSDN. There was no guidance or suggestions provided on what topics these articles should cover. But all but one of the MVPs who provided articles independently chose to write about some aspect of configurations. To me this reinforces my belief that configurations are both one of the most important and least understood features in all of SSIS.

The IT Crowd - Series 3

2008-11-21T10:23:00.001-08:00

I don't watch a lot of TV, but I love the delightful The IT Crowd show put out by Britain's Channel 4. There're aren't a lot of episodes yet, but today that is going to change. The first episode of the new 3rd series is going to air tonight. Those of us in the US will need to wait a while longer, but I'm excited nonetheless. If you're already a fan then you know there's more geek madness in store. And if you're not, you should track down series 1 and 2 and see what you're missing...

A Bad Week to Travel

2008-11-17T11:36:00.001-08:00

I'm on the road this week, in Seattle for the PASS Community Summit conference and in Redmond for meetings and such. Normally I love this trip, because the food is great[1], it's a beautiful area and it's a chance for me to get face-to-face time with more members of my team.

But this week I really wish I were at home, and the reason may not be obvious. It's because I'm going to miss the rollout of the New Xbox Experience. Yeah, I know that's geeky[2] but I've been looking forward to this major overhaul of the Xbox platform ever since it was announced a few months back. Not only does the Xbox UI get a complete makeover, but I will also be able to stream Netflix content directly to my Xbox 360 for instant video gratification.

And it's going to be released this Wednesday, November 19th. And I'm going to miss it until I get home Friday night. And yes, I've been looking forward to it enough to resent those two days enough to blog about it.

Is it Friday yet?

[1] Yes, I'm going back to Elemental@Gasworks this week ;-)

[2] And not like "developer geek" but more like "end user geek" which is a lot less cool-sounding

Vote Early!

2008-11-04T03:55:00.001-08:00

For those of us in the US, today is Election Day. After months of campaigning (I wanted to say "heated debate" but we didn't really get months of that, id we?) it's time to vote, and select a new president.

I live in a quiet little neighborhood, and typically get up early to vote as soon as the polls open. There have never been more than five or ten people in line in front of me, and it's always been a quick in-and-out[1] procedure.

But today was a little different.

Today, when I arrived a few minutes after the polls opened (6:00 AM, still dark outside) the parking lot was nearly full. There were 60 or 70 people already in line in front of me; it took me almost half an hour to get in, vote, and get back out again. And when I left the line was noticeably longer than it had been when I arrived.

Never before have I seen a voter turnout like this one. For some reason, people must feel that this particular election is very important.

So if you're thinking about waiting until after work today to vote, please let me urge you to not delay. If the massive[2] voter turnout that I experienced this morning is at all indicative of what the day will look like at polls around the nation, you will probably want to ensure that you take into account longer lines and longer delays at the polls.

So go vote!

[1] And if you now for some reason feel the urge to go watch A Clockwork Orange then my work here is done. ;-)

[2] Ok, so 60 or 70 people isn't "massive" on a global scale, but please work with me here...

Just One More Reason to Love SSIS

2008-10-31T07:28:00.001-07:00

Alternate title for this post: "Is Sarah Palin BI?" But I didn't want to go there, and I'm sure you appreciate that.

But here in the US, the presidential election season is nearly over (just a few more days to go) and many people are getting sick and tired of listening to the candidates talk. But for the last few months (or years) they have been talking a lot, and people have been transcribing what they say for the press and for prosperity.

And someone (sadly I do not know who[1]) has taken those words, particularly the words of vice presidential candidate Sarah Palin, and run them through the text mining features of SQL Server Integration Services. And the UK IT news web site The Register has the scoop.

What's not to love? Politics, plus killer BI tools, plus dry British humor[2] is a surefire recipe for holiday laughs.

Happy Halloween!

[1] Although if the author of the SSIS package reads this and wants to share the DTSX with me, I would love to have it ;-)

[2] Or does that need to be "humour?"

Join Me in Prague!

2008-10-30T19:41:00.001-07:00

Earlier this week the MCT team here at Microsoft Learning went live with the MCT Summit 2009 web site. There's information about the two currently scheduled summits:

Prague, Czech Republic - Jan 6, 2009 - Jan 9, 2009
Redmond, Washington, US - Jan 20, 2009 - Jan 23, 2009

We're also going to be announcing a 3rd Asia-Pacific region event soon, but the dates and location aren't yet finalized.

But the excitement here[1] today is that I will be attending the Prague event. I wasn't sure until today if I would be able to make it to Prague, but everything has come together and I will be there representing Microsoft Learning in my new Quality Architect role.

And for my own personal pleasure, I'm hoping to do a repeat performance of my "SSIS deep dive" session from February's MCT Summit event in Redmond, and present an "Everything you ever wanted to know about SSIS but were afraid your students would ask" technical session in addition to my Microsoft Learning presentations. The Summit schedule is still up in the air, but even if I can't get on the official schedule I will still do the session if I can find a room and an audience.[2] If you're interested, let me know early, ok?

I'll see you there!

[1] "Here" in this case is my office in New York - I'm sure that everyone at Microsoft Learning in Redmond is probably saying "Oh no, not that guy again!" ;-)

[2] This is what happened in Redmond at the 2008 MCT Summit. Everything came together at the last minute, so there was no way to get on the real schedule, but we ended up with a few dozen MCTs who stuck around for almost four hours of SSIS goodness, even though Microsoft was supplying MCTs with free drinks and food on the other side of town, How's that for commitment?

Certification Offers at PASS

2008-10-29T07:58:00.001-07:00

Are you going to be at the PASS Community Summit 2008 conference next month in Seattle? I am, and you should be too. Why? Well, if the opportunity to attend amazing sessions and rub shoulders with members of the SQL Server product group and SQL Server MVPs wasn't enough, Microsoft Learning is offering a variety of certification-related offers to help PASS attendees get certified on SQL Server 2005 or SQL Server 2008.

First of all, there are two discounts offers available.

If you're among the first 100 to register for an exam, you get the exam for free. This offer has been published for a while, so act now for a chance to get this 100% discount.
If you're not among the first 100, you still get a 25% discount on any exams you want to take during the conference - just register and go.

Next, Microsoft Learning is also offering a set of exam prep sessions for PASS attendees who want an extra edge when taking a certification exam. Here's the tentative schedule (it's really unlikely that this is going to change, but you know how conferences are - you should always check the schedule on site once the event begins) for the exam prep sessions:

Tuesday, November 18

10:15am - 70 432 TS: Microsoft SQL Server 2008, Installation and Maintenance
1:00pm - 70 431 TS: Microsoft SQL Server 2005 - Implementation and Maintenance
2:45pm - 70-447 UPGRADE: MCDBA Skills to MCITP DBA by Using Microsoft SQL Server 2005

Wednesday, November 19

10:15am - 70 448 TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
1:30pm - 70 445 TS: Microsoft SQL Server 2005 - Business Intelligence
3:00pm - 70 455 Upgrade: Transition your MCITP Business Intelligence Developer Skills to MCITP Business Intelligence Developer 2008
4:30pm - Cert 101

Thursday, November 20

10:15am - 70 432 TS: Microsoft SQL Server 2008, Installation and Maintenance (repeat)
1:30pm - 70 450 PRO: Designing, Optimizing and Maintaining a Database Server Infrastructure using Microsoft SQL Server 2008
3:00pm - 70 453 Upgrade: Transition your MCITP Database Administrator Skills to MCITP Database Administrator 2008

As you can probably guess, I'm going to be presenting the three BI exam sessions on Wednesday. They asked me "can you do two?" and I said "no, but I can do three!" The other exam prep sessions are going to be delivered by veteran MCTs Telmo Sampaio and Rob Boek, and the Cert 101 session is going to be delivered by none other than Microsoft Learning's own Gerry O'Brien. What's not to love?

So - if you're going to be at PASS and are thinking about getting certified on SQL Sever, don't miss these offers. And be sure to look me up during the conference. I'll be in the certification area on Wednesday, and will probably be hanging out in the Ask The Experts Lounge at least some of the time (I have so many questions! ;-) and who knows where else I'll be the rest of the time - just keep your eyes open and be sure to say hello.

SQL Server 2008 - Free with Classroom Training

2008-10-29T06:16:00.001-07:00

Talk about synchronicity - the old and the new have come together with a great big bonus for SQL Server professionals who are seeking training on SQL Server 2008. Microsoft Learning[1] is teaming up with the SQL Server[2] team to give away free copies of SQL Server 2008 Standard Edition to people who attend instructor-led classroom training at a Microsoft Certified Partner for Learning Solutions (CPLS). Here's the deal:

1) Attend one of these classes at a participating CPLS between December 10, 2008 (when the courses start to become available) and June 30, 2009:

2778 - Writing Queries Using Microsoft® SQL Server™ 2008 Transact-SQL
6158 - Updating Your SQL 2005 Skills to SQL Server 2008
6231 - Maintaining a Microsoft® SQL Server™ 2008 Database
6232 - Implementing a Microsoft® SQL Server™ 2008 Database
6234 - Implementing and Maintaining Microsoft® SQL Server™ 2008 Analysis Services
6235 - Implementing and Maintaining Microsoft® SQL Server™ 2008 Integration Services
6236 - Implementing and Maintaining Microsoft® SQL Server™ 2008 Reporting Services
6317 - Upgrading Your SQL Server 2000 Skills to SQL Server 2008

2) Get SQL Server 2008:

SQL Server 2008 Standard Edition – Full software
1 Client Access License (CAL)
32 bit, 64 bit and IA64 versions included
Software will be English only at this point

Pretty simple, right? There are a few minor gotchas, but please don't let these stand in your way:

Each CPLS will decide whether or not they participate in this promotion campaign, so talk to your local CPLS and make sure they know about it and that they are participating.
This offer is only good while supplies last. Plan your training early!

And that's that. Call your local CPLS today and ask about SQL Server 2008 training. Tell them Matthew sent you. [3]

[1] This is the "new" part - I joined Microsoft Learning as a Senior Program Manager earlier this month, although I cannot take any credit for this cool promotion.

[2] This is the "old" part - I've loved SQL Server since forever.

[3] And no, I don't get any kickbacks. How unfair is that? ;-)

Holy VHDs Batman!

2008-10-28T09:37:00.001-07:00

Ok, color me impressed. I'm watching today's PDC keynote and just saw that Windows 7 can natively:

Mount VHD files as disks
Create fixed size or dynamic VHDs
Boot from Windows 7 VHDs

Holy crap. That last one totally blows me away - the ability to natively boot from a VHD file to test a virtual machine (and the software it runs) without a host OS sucking down resources. Oh yeah!

Now if they just expand the mounting support to also include standard ISO images, I'll be the happiest geek on the block.

it's not BI, but I'm darned excited!

A Free Font of Wisdom

2008-10-28T09:20:00.001-07:00

One of my Facebook friends (hi Julie!) posted this morning asking for advice on where to find good free fonts. I replied pointing her to one site where I have found many good fonts, and another friend responded with three more. Here's the list:

I know that this isn't BI-related, but I'll bet I'm not the only person who looks for cool and interesting fonts from time to time. And even if I am, now I know where to find a list of links. ;-)

Teaser from PDC: SQL Services

2008-10-27T09:45:00.001-07:00

The core of the PDC keynote has been about Windows Azure, the newly announced "Cloud OS" that Microsoft is showing off. Although this is neat and all, I tend to think of myself as a "data guy" so I've just been nodding and smiling.

Until a few minutes ago, when Bob Muglia presented this slide:

You're probably aware already of SQL Server Data Services. This is a new announcement about the evolution of SSDS, named simply SQL Services. This is a cloud-based service that includes core RDBMS functionality but also analysis, data mining, reporting and ETL. Now that made me sit up and pay attention. (And start typing ;-)

One thing that caught my ear, however, is that while SQL Server Analysis Services and SQL Server Reporting Services were both mentioned by name, SQL Server Integration Services was not. Instead Bob Muglia simply talked about ETL, not SSIS.

What does this mean? Who knows. I could just be focusing on a trivial oversight (I often forget to mention things when I'm on stage, so I know how this works) but I think it will be interesting to see how this plays out. Stay tuned!

Manowar Metal Server 2008 CTP

2008-10-27T07:48:00.001-07:00

If you've read my blog with any degree of regularity over the past few years, you will probably know that I am a fan of the heavy metal band Manowar. Their music is a major source of inspiration in my life, giving me the "warrior spirit" to face head-on the challenges that life brings[1]. But you may not quite understand why.

Well, now you can!

Manowar has just released the trailer for their soon-to-be-released Magic Circle Festival Volume II 2-DVD set. I attended this festival in Bad Arolsen, Germany back in July, and have been waiting for the DVD ever since. It will be released one month from today on November 27th, and you can preorder it at Manowar's online store for just $40.

So if you are looking for a little inspiration in your life, or if you just want to know what the heck Matthew has been talking about all these years, now is your chance.[2] Preorder today!

Edit: And you can also view a high-quality, full screen version of the trailer here.

[1] And it's amazing music too. The inspiration wouldn't quite be the same without the divine vocals, the screaming guitar, the pounding drums and the driving bass.

[2] And before you ask, no, they don't pay me anything.

iWindows 7 Surface Phone?

2008-10-27T06:29:00.001-07:00

I'm not attending the PDC this year, and I must admit that I'm more than a little jealous of my friends and colleagues who are in Los Angeles at the conference this week.[1] I've been following some of the news from the pre-conference online. And one InfoWorld article had a quote that caught my eye. The article itself is about Microsoft opening up the Surface SDK, which is pretty cool in and of itself, but towards then end there was this quote from a Forrester researcher:

"What the iPhone, the Surface, and Windows 7 all have in common is that they are the vanguard of the next major wave of human/technology interaction."

Ok, I get the iPhone and Surface part here - they both have touch-driven interfaces that move beyond what we've seen before in consumer devices - but what about Windows 7? What features is Windows 7 going to include that justifies a market analyst including it in the same category as the Surface and the iPhone for human/technology interaction?

Microsoft has been playing Windows 7 pretty close to the vest, and this week's PDC is likely to be the first time that many people outside of Microsoft get to see it. I suppose that now that I am working for Microsoft[2] I could download and install it, but honestly I have too many other things to get done. So for now I'll just keep watching the press.

And now that this tidbit has piqued my interest, I will definitely be watching the PDC keynote in two hours. You should too.

[1] Although when I learned that it was Ozzie and not Ozzy delivering the keynote with Bob Muglia, I was a bit less disappointed to not be there. Ray just can't hit those high notes the same way...

[2] Three weeks today!

T-SQL Fundamentals is (Almost) Here!

2008-10-14T16:01:00.001-07:00

Not too long ago I posted about Itzik Ben-Gan's excellent book Inside Microsoft SQL Server 2005: T-SQL Querying and how valuable I've found it. Well, Itzik has just completed work on his next book: Microsoft SQL Server 2008 T-SQL Fundamentals.

This is a book for people who are new to SQL programming, which is an audience that Itzik has not tackled before. Now you might think that for someone who knows more about T-SQL that anyone else in the world, writing a beginners' book should be about as difficult as sleepwalking, but this is not the case. In a recent post to the private SQL Server newsgroup for MCTs[1] Itzik had this to share:

“I always wanted to write a book about T-SQL Fundamentals, but kept postponing it until I felt I acquired enough knowledge and teaching experience to write it. Well, you never feel you have enough knowledge especially with a language and a model that are so deep, but at least enough to make a decent effort. Some may think that writing a Fundamentals book is easier than writing an advanced one, but I think it's actually the other way around, especially with SQL. Target audience for advanced books is less prone to be misled and mainly need their gaps to be filled. It's a big responsibility to teach people fundamentals, and that's one of the reasons I waited so long.”

How cool is that? Even though I don't think I'm the target audience for this book, I am definitely going to get a copy. As a trainer I'm always looking for better ways to explain core concepts, and T-SQL has enough difficult concepts that I am sure to pick up a pointer or two (or two hundred) from this book.

The book is scheduled to be released on October 22 and you can pre-order it today. So what are you waiting for?

[1] Yes, I asked his permission before posting this quote here. ;-)

Silverlight 2.0 RTM!

2008-10-14T08:16:00.001-07:00

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.

Fame! Fortune! Featured Blog!

2008-09-16T16:24:00.001-07:00

I just received an email notifying me that my BI Polar blog has been featured in the "Bloggers Spotlight" section on the TechEd Bloggers web site. This site aggregates a bunch of technical blogs from bloggers who participated in Microsoft's TechEd conference. I've had a bunch of my posts (not the ones about fine dining and finer music, just the technical ones) show up there but it's a nice surprise to have the whole blog featured.

On a related note, I definitely do continue to blog on BI and SSIS topics (and all of the other random stuff that justifies the "BI Polar" title) after I join Microsoft next month. I'm sure my post frequency will continue to be erratic, or even if but the blog will continue to be hosted here on blogspot, but rest assured the madness will continue. I see several interesting places to apply BI technologies to the challenges waiting for me at Microsoft, and if I know me (this is not guaranteed, but it does seem likely) I'll find the time to blog about it here and there...

Losing My MVP

2008-09-15T17:26:00.001-07:00

Since the beginning of the year I've had the Microsoft Most Valuable Professional (MVP) logo on my blog. I've been delighted to be honored with the MVP award, because is is an acknowledgment of the effort I've made to give back to the Microsoft technical community. This is what Microsoft has to say about the MVP award:

"The Microsoft MVP Award Program recognizes and thanks outstanding members of technical communities for their community participation and willingness to help others. The MVP Award is given to exceptional technical community leaders who foster the free and objective exchange of knowledge by actively sharing their real-world expertise with technology users. The MVP Award celebrates the most active community members from around the world who provide invaluable online and offline expertise that enriches the community experience and makes a difference in technical communities that feature Microsoft products."

What a cool thing to have Microsoft say about you, right? But in another three weeks I will no longer be an MVP - Microsoft is taking the award away from me.

Why would they do that, you ask?

Well, since I haven't been violating any NDAs lately, that narrows down the field. Have you guessed yet?

That's right - I'm going to be joining Microsoft as a full time employee. MVPs are "independent experts" and Microsoft employees cannot be MVPs.

Starting October 6th I will be joining Microsoft Learning as a Senior Program Manager/Quality Architect. I will be working with Microsoft Learning's Courseware Development Group (CDG) to improve the quality of Microsoft Official Curriculum (MOC) courseware. This is a challenge that is very close and dear to my heart. I've been a Microsoft Certified Trainer (MCT) since 1996, and have taught many MOC courses over the years. The quality of the courseware is a big factor that affects trainers (and their students) around the world, and being in a position to make such a big difference to such a large audience is an opportunity I simply could not refuse.

And I get one of those cool blue badges - how cool is that? ;-)

My Interview With Chris Shaw

2008-09-15T14:53:00.001-07:00

Chris Shaw is the conference director for the SSWUG vConferences that I have been blogging about for the past few months. Last week he sent out a set of interview questions to the various conference chairs[1] asking questions about careers and technology and such. I replied, and today Chris posted the interview on his blog, here:

Business Intelligence with Matthew Roche

Some of the questions[2] were quite interesting, so if you have a spare minute or two you should feel free to check it out.

[1] Not the things you sit in, but so far as I know this is the accepted gender-neutral form of "chairmen" as opposed to "chairpeople" or something...

[2] As opposed to the answers, of course

Business Intelligence vConference Update

2008-09-15T13:56:00.001-07:00

If you've been following my posts about the SSWUG Business Intelligence vConference, you know that it's an online conference with 10 world-class speakers and 30+ sessions that cover the breadth of the Microsoft SQL Server BI stack.

But it just got even better better.

Based on popular demand from conference attendees, the BI vConference and its sister SharePoint, SQL Server and .NET vConferences will be consolidated into a single SSWUG Ultimate Conference[1] that includes all 120+ sessions from 40 industry-leading speakers

And you get all this for the $100 price tag of the BI-only conference.

The new conference dates are November 5, 6 and 7, so you also have some extra time to prepare for the show. Tell your friends, and I'll see you all there.

[1] I voted for the name of "ÜberCon" but got vetoed on this one...

Elemental, My Dear Watson

2008-09-13T14:14:00.002-07:00

This is a completely non-technical post, but since I know that quite a few people who read my blog also live in the Seattle area, so there is no way I could not post it. You see, I had the most amazing dinner last night...

My friends Ken and Susan had each independently recommended the restaurant, Elemental@Gasworks[1] to me and I'd been wanting to check it out for some months now. And since I was in Seattle and free last night, I had my chance. And let me tell you, my expectations were sky-high, and I was not disappointed.

I arrived around 4:15. I'd heard that the place was hard to find so I wanted to give myself some extra time, but between my GPS and the verbal instructions I'd received from Ken I did ok. I took advantage of the opportunity to visit the Gasworks Park which is just south of the restaurant, and took my seat around 5:00 when the doors opened[2] for business. There was a cocktail hour from 5:00 to 6:00, but since I was driving I couldn't partake in the hard stuff, and instead sipped some iced wine and cucumber water[3] while snacking on my truffle popcorn.

Instead of ordering from the menu I took the prix fixe option. I'd heard enough good things about the chef to trust her judgment, and what followed was an exercise in culinary delight. Let's see...

We started with stuffed calamari (the body of the squid was stuffed with rice and other goodness, and the tentacles were on the side) cooked in red wine,
Which was followed by a blue cheese and pear bread pudding, which was savory and sweet at the same time,
And then a dish of lamb tartare with a quail egg yolk on top, served with crispy salty cheese cracker triangles,
Followed by a green salad with a truffle vinaigrette dressing,
And a salmon filet cooked with bacon and served on some sort of wilted greens and pear tomatoes,
And finished up with a roast quail stuffed with something delicious - by that point it was too dark for me to tell just what it was.
Of course "finished up" is something of a misnomer, because I opted to get the cheese course as well, which included four types of amazing cheese, three varieties of wine, some bread, slices of apple, and a date stuffed with nuts and other goodness.
And by then there was no way I could say no to the "sweets," which included three different desserts and a pair of dessert wines.

Oh my goodness - was the best meal I've had in months, and perhaps the best meal I've ever had in the US. Yes, it was that good.

I just wish I had a designated driver, so I could have done more than sip my wine. The wines were generous and paired perfectly with the courses, but since I was driving I had to limit my intake to just a taste here and a taste there.

And the last thing that blows my mind - the meal cost $87 in total. Now I know this is not a cheap meal, but this includes the prix fixe menu ($40) plus the wine pairings ($20) plus the cheese course ($12) plus desserts ($15) and includes taxes. And they don't accept tips, so when I say "total" that's exactly what I mean. I honestly don't know how they do it. I've paid twice as much for meals that were far inferior.

If you're in the Seattle area, definitely check out this restaurant. You need to show up early (doors open at 5:00 and every table was full by 5:15 or so, and at 9:00 I was the first one to leave) or show up late (to get a table when the first round of diners is done) but definitely show up. I know that I'll be back...

[1] As I write this the restaurant web site is down, so I'm linking to a Seattle Times review instead. Normally you can find them online here http://www.elementalatgasworks.com.

[2] Figuratively - the doors were physically open the whole time.

[3] That's right - there was a little slice of cucumber in the carafe of ice water on the table, and it gave the water a very subtle cucumber flavor. It was a little weird, but it worked.

Views as "Interfaces" for SSIS

2008-09-10T22:44:00.001-07:00

This is a technique that I have used for quite some time, and while I have mentioned it to others often enough, I have never blogged on it. A recent conversation on the MVP newsgroups made me think of it again, and hopefully I'll get the chance to type it all up before I get pulled back to the real world.

Here's the scenario:

An SSIS package is extracting data from a 3rd party database.
The package is deployed to multiple dev/test/prod environments.
The package uses configurations so that deploying to multiple environments does not require changes to the package file.
The database schema of the various environments is "identical." [1]
The package works in one environment, but when deployed to another environment, it fails validation with the dreaded "VS_NEEDSNEWMETADATA" error.
After various debugging and diagnostic steps are completed, the package suddenly works again, even though "nothing has been changed."[2]

Hey wait - if the database schemas are identical, you shouldn't get this error, right? And since nothing has changed, the error shouldn't just disappear. What's going on here? Obviously SSIS shouldn't behave like this.

Of course, the real world story here is that someone was changing the schema of the test environment and had not communicated that change to anyone. Once this is known, the solution is pretty obvious, and it's clear that SSIS is behaving as desired, enforcing strict typing in the data flow.

But if this was a simple "user error" scenario it wouldn't be appropriate blog fodder, would it?

But unfortunately, this is not a rare, one-off scenario, and the solution generally involves more communication than it involves technology. But before looking at a solution, let's see if we can summarize the problem. These are the significant problem factors that I see when I look at this scenario:

The SSIS data flow is incredibly strict when it comes to data types. This strictness is closely related to interfaces in object oriented programming in that the names, order and data types of the columns being consumed cannot change in any way once a client (the SSIS data flow) exists that is built to use the interface.
When the "interface" of the source data changes, any data flows that reference it will break with the "VS_NEEDSNEWMETADATA" error mentioned above.
Database administrators are generally pretty good about not changing column names and data types on tables when they know that other database objects rely on those tables.[3]
There is no simple/easy/straightforward way for a DBA to tell if an SSIS package uses a given table. Solutions generally rely upon documentation that is external to the database, or just waiting until something breaks.

So how does this problem description help us reach a solution? Take a look at the last two bullets and the solution practically presents itself: Use database views as an "interface layer" within the database itself.

But what does this mean? In short, it means that:

The SSIS data flows should extract views from views and not from base tables.
The views should "self-documenting" to DBAs because they are database objects with concrete dependencies with the base tables from which they draw their data.
The views should explicitly CAST each column to an explicit data type and column alias - even when the alias name and data type exactly match the base table - to provide an additional layer of insulation against changes to the base tables.

The key here comes from the second and third bullets, above. Having the views as "clients" to the base tables should make it more obvious to people who are changing table schemas that the the tables in question are being used. And the explicit data type casting in the view definitions will define the "interface" required by the SSIS package clients in a way that is familiar to DBAs.

Now obviously this isn't always an option - many DBAs will not allow the creation of new database objects (such as views) but when this is viable it is a technique with which I have had great success. And when it's not, you can still get some of the same benefits (namely the data type change protection) by basing your data flow on a source query that explicitly casts and aliases each column in the SELECT statement. Then, so long as data type changes to the underlying tables can still support an explicit cast to the data type required by the SSIS data flow, the package still won't break.

So there you have it - a technique to isolate SSIS data flows from changes in source databases, and to reduce the likelihood of those changes happening in the first place.

What do you think? I'd love to hear if anyone has used similar techniques...

[1] Yes, this is in quotes for a reason

[2] Same thing here

[3] This is a pretty broad statement, I know...

SSIS Sample Data Flow Transformations

2008-08-30T08:39:00.001-07:00

Every few months I stumble across this old blog post by Euan Garden[1] that links to a bunch of sample transformation components that the SSIS team put out in the 2005 release timeframe. Check it out: http://blogs.msdn.com/euanga/archive/2006/02/14/IsTransforms.aspx

Now obviously this isn't new news, but I can never find it when I'm looking for it, so hopefully it will help me and you the next time one of us is looking for sample transformations...

[1] Always when I'm looking for something else entirely.

New SSIS Article Online on MSDN

2008-08-28T17:21:00.001-07:00

I have a new article on the SSIS Developer Center on MSDN, focusing on Data Sources and Configurations as tools for connection reuse across sets of SSIS packages. I wrote it months ago (looking at it now it seems even longer[1]) but it just made it online today.

Check it out here: http://msdn.microsoft.com/en-us/library/cc671619.aspx

This is actually one of a set of articles by SSIS-focused SQL Server MVPs that will be published over the next week of so - I just could not wait to let people know that it was out there. I'll post links to all of the articles once they're all online (and I probably won't be the only one to do so) but for now you can get started with this one. Enjoy!

[1] For example, the "about the author" blurb at the bottom lists me as still working at Configuresoft, even though I have not been working with Configuresoft since the end of July.

Who Runs The Cloud?

2008-08-28T16:40:00.001-07:00

Cloud Computing is a very popular phrase these days, and it's been creeping its way into SQL Server circles with the introduction of SQL Server Data Services and other service offerings from Microsoft and other companies. It seems to be what many IT managers and some IT professionals want to talk about.

But it's always struck me as being a little odd. After all, I've never met either:

A) Software that runs on water vapor and ice particles suspended in air,

or

B) A cloud that's made up of high-end computer hardware.

Yes, I know - that's not really fair. Everyone knows that the "cloud" in Cloud Computing refers to the Internet, which is usually drawn as a big fluffy cloud on whiteboards because it's too complex and dynamic in structure to be diagrammed accurately at more detailed levels of abstraction. But still, it seems to me that too many people fail to look deeper, to force the "cloud" abstraction to leak, as it were.

Until it leaks on them.

Obviously, having your software run "in the cloud" means that it's running in a well-connected data center, probably one that's managed by a 3rd party. But the cloud is still made up of hardware and software, and regardless of who owns and operates it, it still needs to be managed by someone. And what happens when that fails?

Well, bad things, right? I came across this article on The Register[1] today:

http://www.theregister.co.uk/2008/08/28/flexiscale_outage/

Ouch.[2]

What's the moral of the story? I'm not going to claim that I know, but I'm sure that it is something related to "good for the goose, good for the gander" or the like. The same bad things that can happen to your own data center can happen to others' data centers as well, even when your applications and services are running there.

So is it time to look at your SLA again?

[1] My favorite source for bitter and sarcastic IT news

[2] Ouch and a half.

Buy This Book, or Your Database Gets It!

2008-08-28T12:29:00.001-07:00

At last night's VDUNY meeting I talked about a whole bunch of new features in SQL Server 2008 for database developers. And because this is a favorite topic of mine[1] a lot of the discussion revolved around Transact-SQL.

And you can't really talk about Transact-SQL without the name Itzik Ben-Gan coming up at least once or twice. Itzik is arguably the person who knows more about using T-SQL than anyone else on the planet, including the people who develop the language at Microsoft.[2] And of course, you can't mention Itzik at a user group without recommending this book:

Inside Microsoft SQL Server 2005: T-SQL Querying

If you develop applications using SQL Server - any version, not just SQL Server 2005 - then you absolutely must have this book in your library. It doesn't matter if you are brand new to SQL Server or (like me) you have been a SQL Server consultant and trainer for over ten years - you will find information in this book that you never knew you didn't know, and you'll find techniques that will make you a better SQL developer every day.

Yeah, it's that good.

Just to make sure no one thinks that Itzik is here behind me with a gun, this is a purely unsolicited recommendation. I'd just gotten a follow-up email from an attendee, which made me remember the presentation, which made me remember the discussion, which made me remember just how darned good this book is. Buy it and see.

[1] And because they were foolish enough to say "talk about whatever you'd like." ;-)

[2] Yes, this is a powerful statement, but I feel comfortable making it because A) I did qualify it with the word "arguably" and B) I've heard enough people on the SQL Server team say the same thing without using the word "arguably."

Working Around AT&T's "Help"

2008-08-27T11:35:00.001-07:00

I own an AT&T Tilt smartphone. I use it for all sorts of things, including voice calls, web browsing and email, and it's very rare that I leave home without it. But one thing that I have not used it for before today was connecting to the Internet from my laptop. Earlier this month I upgraded my AT&T plan to include "tethering" to allow me to do just this. I spent half an hour or so on the phone with a very helpful young lady at AT&T tech support who walked me through what I would need to do to set up and use tethering, including this and this. Life was good.

Except for the fact that it didn't work.

Following AT&T's "help" allowed me to connect my laptop to my phone, but not actually to the Internet. And today, since I'm preparing to be at the Rochester Microsoft offices for tonight's WDUNY meeting, I figured the time was right to figure out what was going on. I was getting ready to call AT&T support again to see what they had to say when I realized that this might not be the best approach. So instead I fired up my favorite search engine and found this and this. And by following the instructions on the second page, I was up and running - connected to the Internet from my laptop via my phone, and connected to my VPN from there, and talking on the phone at the same time - within 10 minutes.

Thank goodness for the Internet. But why did it need to be this hard? Why is the "official" solution obtuse and backward and (to top it all off) non-functional?

I don't have an answer, but with a little luck the next time someone is trying to tether his Tilt, he'll have one more resource to help him get going. And hopefully this solution will keep working, since I'm going to be on the road a lot over the next three or four weeks...

VDUNY Meeting Tonight

2008-08-27T09:35:00.001-07:00

Just as a quick reminder, I will be presenting on new features in SQL Server 2008 at the Visual Developers of Upstate New York user group tonight at 6:00 PM in Rochester, New York. If you're in the Rochester area, be sure to attend.

And remember - as an added bonus I will be giving away a set of post-conference DVDs from this year's TechEd conference. This is a set of nine DVDs with all of the breakout sessions and keynotes from both the TechEd Developers and TechEd IT Professionals conferences, with a retail value of $195. It could be yours!

One More Reason to Attend

2008-08-27T07:18:00.001-07:00

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

2008-08-26T12:19:00.001-07:00

(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...

Cake Wrecks

2008-08-22T10:09:00.001-07:00

This has nothing to do with SSIS or SQL Server or technology at all, but I need to post it anyway. My friend Karla just shared this link with me:

http://cakewrecks.blogspot.com

You probably know that I am an avid baker and a general wise-guy[1] but I never thought to bring the two of them together quite like this. The Cake Wrecks blog is dedicated to... let's just call them "unfortunate incidents" in the cake decorating world. Funny pictures, amusing commentary and fun - what's not to like?

[1] And that most people who know me generally spell "guy" with an "a" and a repeating "s"

Waltham Code Camp 10: "Dev InTENsity!"

2008-08-21T13:49:00.001-07:00

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

2008-08-21T10:15:00.001-07:00

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.

SSIS in Stockholm 2.0

2008-08-20T08:21:00.001-07:00

Last autumn I visited the beautiful country of Sweden for the first time. I delivered a two-day SQL Server Integration Services "advanced topics" seminar in Stockholm and presented at the Swedish SQL Server User Group one evening as well.[1] I had a great time and based on their evaluations the seminar attendees did too.

So we're doing it again.

On October 1 and 2, I will be back in Stockholm for another SSIS seminar. The outline looks like this:

SSIS development best practices
SSIS deployment best practices
Extending SSIS packages through custom .NET code using the Script Task and Script Component
Using open source tools to enhance the SSIS development lifecycle
Building a custom configuration solution to move beyond the built-in configuration features
"Elegant solutions for common problems" utilizing SSIS expressions to build real-world packages
Adding data mining to your SSIS packages
Performance tuning the SSIS data flow
New SSIS features in SQL Server 2008
Lots of opportunities for Q&A and real-world SSIS discussion

It's always a real joy for me to speak on my favorite topic (SSIS) and this seminar is going to be doubly exciting. Not only do I get to return to Stockholm, I also get two full days to present on some of my favorite subjects, do lots of hands-on demos, share some cool code, and above all share my passion and excitement for the SSIS platform with everyone involved.

So if you're going to be in Stockholm at the beginning of October, you should definitely plan on attending this event. And if you're not going to be in Stockholm, you should plan on coming anyway. The city is beautiful this time of year, the food is amazing, and the seminar content will be even better.

[1] I also had some of the best food I've ever eaten, got to explore a truly beautiful city, and meet some really nice people.

SSIS Deployment at TechEd Online

2008-08-20T08:00:00.001-07:00

The third and final "Tech*Talk" video interview I recorded during Microsoft's TechEd conference this June in Orlando is now online. I talked with INETA board member Mark Rosenberg about SQL Server Integration Services deployment. You can check it out on the TechEd Online video library site in various formats:

Enjoy!

Building Packages Programmatically...

2008-08-20T07:46:00.001-07:00

...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:

The API for working with the SSIS data flow is a bit complex.
The documentation on the SSIS data flow API is a bit sparse.
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.

BIDS Helper 1.3 Release

2008-08-16T06:15:00.001-07:00

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.

SQL Server 2008 is Released to Manufacturing!

2008-08-06T12:01:00.001-07:00

SQL Server 2008 went RTM this morning and is currently available from the MSDN and TechNet Subscriber download centers. I'm downloading now - are you?

I assume you're not, since I'm getting a sustained download rate of over 2100 KB/sec. Maybe I should wait until my download is complete to hit "Publish." ;-)

SSIS for IT Professionals

2008-07-28T09:06:00.001-07:00

One of the "Tech*Talk" video interviews I recorded during Microsoft's TechEd conference last month in Orlando is now online. I talked with Joey Snow of the TechNet Edge web site about SQL Server Integration Services for IT Pros. You can check it out on the TechEd Online video library site in various formats:

Enjoy!

SSIS Community Samples on CodePlex

2008-07-23T13:07:00.001-07:00

The SSIS team has just published the first two of a set of community samples to the CodePlex site. Take a look here: http://www.codeplex.com/SQLSrvIntegrationSrv

The exciting thing is that these samples include both a binary redistributable and source code, so you can use them as-is if they serve your needs, and customize them if they only get you part way to your destination. The first two samples are an XML Destination component (this is an oft-requested data flow destination, so this is probably going to be the big crowd pleaser) and a Regular Expression Flat File Source component that provides many capabilities above and beyond the built-in Flat File Source.

You can check out the features on the release page here: http://www.codeplex.com/SQLSrvIntegrationSrv/Release/ProjectReleases.aspx?ReleaseId=15424

Enjoy!

Check Out This Lineup!

2008-07-05T10:37:00.001-07:00

I've posted before about the Business Intelligence Virtual Conference I'm helping to organize. Even though I have not had much to say about this exciting event in the last few weeks, this doesn't mean that I haven't been feverishly busy making sure that the conference will be great. We're still finalizing the session schedule, but we have the speaker list nailed down[1]. Check this out:

Donald Farmer: Donald is the Principal Program Manager for SQL Server Data Mining at Microsoft and was the Program Manager for SQL Server Integration Services for the SQL Server 2005 RTM release. Donald is always a much sought-after and highly rated speaker, especially when he's talking about his favorite topics like data mining and fish farming.[2]
Brian Knight: Brian is a SQL Server MVP and the author of multiple books on SQL Server Integration Services. He's presented regularly at major conferences like TechEd and PASS, and is a great speaker all around.
Ted Malone: Ted is a Visual Studio Team System MVP, but knows more about the Microsoft BI stack than most SQL Server MVPs I know. Ted is also a great speaker who has presented at various conferences on lots of SQL Server related topics.
Matt Masson: Matt is a developer on the SQL Server Integration Services team at Microsoft, and worked at Cognos before joining Microsoft. As an SSIS insider, Matt has great insight into the inner workings of the product, and will be sharing them during his sessions.
Sonya McNeal: Sonya is a Microsoft Certified Trainer and consultant who specializes in the Microsoft BI stack. She presented some of the highest rated instructor led labs at the TechEd conference in Orlando this June, and will be bringing her many years of training and presenting experience into play for the virtual conference.
Scot Reagin: Scot is a SQL Server MVP and a mentor with Solid Quality Mentors with more than 20 years experience in the database and BI field. Scot has presented at many major conferences including TechEd, PASS and SQL Connections.
Matthew Roche: If you're reading my blog hopefully you have some idea who I am, but just in case, I'm a SQL Server MVP, MCT and experienced BI speaker and consultant. I'm honored to be the conference chair for this conference, and will be doing everything in my power[3] to ensure that this conference sets the bar for BI conferences to come.
Craig Utley: Craig is a mentor with Solid Quality Mentors, and used to be a Program Manager on the SQLCAT team at Microsoft and is the author of several books. These guys are the best of the best - they're the ones that get called in when no one else can solve the problems. Craig is also a regular presenter who can make even the most complex BI topics easy to understand.
Erik Veerman: Erik is a SQL Server MVP and a mentor with Solid Quality Mentors who has co-authored several books on SQL Server Integration Services and is responsible for the SSIS ETL best practices in Microsoft's Project REAL. Erik is a regular author and presenter on all facets of the Microsoft BI stack.
John Welch: John is a SQL Server MVP and is the Chief Architect at Mariner, where he is responsible for the full end-to-end Microsoft BI stack. John is an experienced presenter with deep insight into all of Microsoft's BI products.

What an amazing lineup - I can't adequately express how excited I am to be working with this team. Each speaker will be presenting three sessions (and I'm just as excited about the session list as I am about the speaker list - I can't wait to share it with you) for a total of 30 sessions plus three keynote presentations - one for each day of the conference.

And remember - the entire virtual conference is just $100 for the full three days, and as I mentioned in an earlier post, if you attend the virtual conference you also get a $150 discount off the Dev Connections Fall 2008 Conferences this November in Las Vegas.

How could it get any better than this?

[1] As of this writing, the speaker list on the conference web site isn't complete - we're still waiting on a photo from Matt Masson, but everything else is there.

[2] Don't ask. Trust me. ;-)

[3] And of course, because I listen to Manowar, my power is pretty much limitless.

SQL Connections, Revisited

2008-07-05T09:58:00.001-07:00

I just posted about the two breakout sessions I'll be presenting at the Fall SQL Server Connections Conference in Las Vegas. Just in case you're wondering if it's worth it, I want to share with you some attendee comments from my session on SSIS deployment at the Spring SQL Server Connections Conference in Orlando:

Good presentation skills.
Great ideas on guidance I can apply immediately.
Too crowded! Tables?
Room was way too small.
Without question, the best session of the conference. If I had learned nothing else, it would have been worth the trip for his alone.
Great speaker.
Really tested me on program terms. Presenter was fabulous!
Good jokes.[1]
Outstanding!

As you can see, we had an excellent time in Orlando. We were a little crowded - it was standing room only room - but we didn't let that slow us down, and now that the conference organizers understand just what a big draw SSIS can be, we should not run into the same problem in Vegas.

Do you notice anything else about these comments? Everyone had fun. SSIS is such an exciting and interesting topic[2] that people had no choice but to learn a lot. And of course the fall sessions are only going to be better, so plan on being there!

[1] I'm not honestly sure what this guy was smoking - this is a comment I've never seen before. ;-)
[2] Well, I think so anyway, and since I'm the one on stage for these sessions my passion will be contagious - if you're there you'll catch it too.

What Happens in BIDS, Stays in BIDS

2008-07-05T09:48:00.001-07:00

That's right - it's time to start planning for the Fall 2008 SQL Server Connections Conference in Las Vegas. It's going to be held at the Manadalay Bay Resort and Casino from November 10th through November 13th, and there are lots of excellent sessions scheduled. In fact, I will be delivering two SQL Server Integration Services sessions:

SQL Server Integration Services Development Best Practices
Are you tired of feeling like you’re making the same mistakes over and over again? Would you like to have a roadmap that outlines the pitfalls you’re likely to encounter when building ETL solutions with SSIS? Then this session is for you! You’ll learn how to get the most from the SSIS tools and platform through a set of SSIS development best practices from a battle-scarred database and BI consultant who has survived the rough projects and lived to tell the tale.

SQL Server Integration Services Performance Tuning and Optimization
SSIS packages have many capabilities, from control flow to event handlers to scripting. But the SSIS data flow is where the decisions you make will have the greatest impact on the performance of your packages. In this session, you’ll learn what’s going on under the hood in the SSIS data flow pipeline, and how to take advantage of that knowledge to make your packages perform better. You’ll also learn general tips and tricks to improve SSIS package performance and how to get the most out of your packages.

Sound like fun? Well, it gets even better!

Do you remember the Business Intelligence Virtual Conference I mentioned a few weeks back? I'll post more information about the virtual conference once we get the session schedule finalized, but for now you should know that if you attend the Business Intelligence Virtual Conference - $100 for three days worth of amazing content - you will get a $150 discount for the Fall SQL Server Connections conference.

How cool is that? If you're planning on attending the SQL Server Connections conference (or any of the Dev Connections conferences being held at the same time, because a ticket to one gets you admission to all of them) then we're essentially paying you $50 to attend the Business Intelligence Virtual Conference. That's like $50 better than free, which in my book is pretty darned cool.

So start planning now, and I'll look for you in Las Vegas!

Data Visualization Gone Wrong

2008-07-03T16:21:00.001-07:00

Or perhaps gone horribly, horribly right.

http://graphjam.com

I must say "thank you" to Ken for sharing this URL. We all knew that charts and graphs could be staggeringly funny, but we never knew just how funny they could be. Until now.

Outlook and Time Zones

2008-07-01T12:25:00.001-07:00

I travel quite a bit for work (and occasionally for pleasure) and I like to track my itineraries in Outlook so I don't lose track of things and miss my flights. This also allows me to have everything at my fingertips on my Windows Mobile phone, which makes my life so much simpler.

The problem with this is that most of my travel crosses multiple time zones - I will be departing from New York, but I may be landing in Seattle or Denver or Frankfurt. And that means that I need to "do the math" to ensure that the times I'm entering into my Outlook appointments are appropriately adjusted for the differences between my local time zone and the time zone for wherever I will be landing.

But not any more.

I've just discovered[1] the "Time Zone" button in Outlook 2007:

[2]

When you click on the Time Zones button, two drop-down lists appear, one for the time zone of the appointment start time, and one for the time zone of the appointment end time. How cool is that? This is going to save me so much time (and aggravation, because there few things more annoying than suffering because of your own mistakes) in the weeks and months ahead.

Now if I could just figure out how to make it so that these drop-down lists are always visible by default...

[1] Yes, this is the appropriate time to let me know that this has been there since Outlook 2000 or the like - I am often the last to know.

[2] I'm not going to show you one of my actual flight itineraries (you never know what stalkers are out there ;-) but I will show one of the highlights of the yeah - Manowar headlining the Magic Circle Festival next month in Germany. Will you be there too?

Congratulations, Ted!

2008-07-01T09:07:00.001-07:00

Congratulations are in order - my friend and colleague Ted Malone has been awarded the Microsoft Most Valuable Professional award for Visual Studio Team System. So take a moment and send Ted a word of congratulations, and remember to ask him "What's a NUMA?" while you're at it.

Keep up the great work, Ted!

SSIS - Changing Variable Scope

2008-07-01T08:03:00.001-07:00

If you read my last blog post about SnagIt, you already know that when I was creating the package for my "putting dates in file names" blog post, I inadvertently created the variables at the data flow scope, when I needed to create them at the package scope instead. And if you've spent much time working with SSIS, you know that this means I had to delete all of the variables and re-create them, setting all of their properties and expressions all over again.

What a pain. The SSIS tools in Visual Studio will let you change just about any property of a variable, but not the variable scope. There should be some tool to do this, right?

Right!

Well, this is a new feature that is being added to the BIDS Helper Visual Studio Add-In project on CodePlex. Check out the details here: http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Variables%20Window%20Extensions&version=2

As with quite a few interesting recent additions to BIDS Helper, this feature is not yet part of a major release, so you'll need to download the source code and build it yourself, but this is relatively simple. Hopefully the project team will have version 1.3 locked down and ready for public release soon, but in the meantime you can get it if you want it. I just wish I'd had it when I was building that sample package...

SnagIt++

2008-07-01T07:51:00.001-07:00

I blogged on TechSmith's delightful SnagIt application almost a year ago and have been using it daily ever since. SnagIt is one of those applications that's kind of hard to describe (why would anyone spend money for something like this when you get Print Screen and Paint for free with Windows, right?) but once you start using it you wonder how you ever lived without it. Every blog post that you've seen here[1] that has screen shots has been written with the help of SnagIt.

And now it's gotten a lot better.

Last month TechSmith released SnagIt version 9, with a whole slew of new features and capabilities. There are too many to list here, so you can check out the What's New page if you're interested - the thing that blows me away is the capture organizer - when you capture an image, it goes into a tray where you can save it, edit it, delete it or just plain ignore it if you're not ready to do anything else just yet. This provides an amazing degree of flexibility, and makes working with a large number of captures (such as documenting a I workflow through still images) a trivial task.

As a case in point, my last blog post has a total of 19 screen captures, some of which were taken out of order[2]. Think about how you would traditionally do that; if you're like me,this would involve saving the files with sequentially-numbered file names, and then re-naming them as necessary. With SnagIt 9 this was simply a matter of capturing all of the images I thought I might need, deleting the ones I didn't need, and then saving the ones I decided to use once all of the writing was done. I love it.

And the best thing is that TechSmith offers a free trial version for download, and the full version is only $49.95. And no, they're not paying me anything for this recommendation - SnagIt is just one of those tools that I use every day that not enough people seem to know. Check it out and see for yourself!

[1] Or forum posts, book chapters, or articles - anything!

[2] I inadvertently created the variables I used for this post at the wrong scope, so I ended up going back and deleting and recreating them, and had to re-capture all of the images of the Variables window. What a pain.

Looking for a Date - What's in a Name?

2008-06-27T09:31:00.001-07:00

I've blogged about using SSIS to create text files with dynamic file names before, but there's another scenario that keeps coming up time and again. It looks something like this:

"I need to export data from a database into a text file. I have this working, but I need the text file to have a name that's based on the current date, like MyOutputFilePrefix_YYYY-MM-DD-HH-MM-SS.txt. How do I do this?"

It should come as no surprise that the short answer is "use expressions." Expressions are (in my humble opinion, of course) the single most powerful feature in the SSIS developer's toolbox, and are the primary mechanism for adding dynamic functionality to SSIS packages.

Bur of course "use expressions" doesn't give you all the information you need, so here is a quick walkthrough that shows all of the steps involved.

Start off by creating or locating a flat file that has the format that you need. This will give you a head-start for configuring your Flat File Connection Manager later on. Since I'm working on a simple demo, my text file is appropriately simple:
Next, add a Flat File Connection Manager to your SSIS package, and update its properties to reference the sample flat file you created above:
Make sure that the columns you defined in the file are being parsed correctly:
And update the properties (generally the data type) of the columns as needed. In this scenario, we're setting the data type of the ID column to be a four-byte signed integer:
Next, add an OLE DB Connection Manager (or whatever makes sense for your source database) to your package, configure it as necessary[1], and add a Data Flow task to the package as well:
Inside the Data Flow task, add an OLE DB Source component (or whatever makes sense for your source database) and configure it with your source query:
Next, add a Flat File Destination component to the data flow and connect it to the source component:
Configure the Flat File Destination to use the Flat File Connection Manager and ensure that its column mappings are configured correctly:
Run the package to make sure that it works:
And test it by checking the contents of the target text file:
And now we're ready to start. That's right, the last 10 steps were included to show how I got to the starting point for adding the dynamic functionality that is the whole point of the post. What we've done above is set up a simple package and data flow that exports data to a text file, which is (I assume) where the people who keep asking the question at the top of the post are getting stuck. And since we walked through it step-by-step together, everyone can see that there is no prestidigitation involved.
So let's start off by creating three different package variables, Foldername, FileName and FilePath:
Set the value property of the FolderName variable to the path of the folder where you want your files to be created:
Set the EvaluateAsExpression property of the FileName variable to True and set the Expression property to this expression:

"MyOutputFilePrefix_" + (DT_WSTR,4)YEAR(@[System::StartTime]) + "-"
    + RIGHT("0" + (DT_WSTR,2)MONTH(@[System::StartTime]), 2) + "-"
    + RIGHT("0" + (DT_WSTR,2)DAY( @[System::StartTime]), 2) + "-"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("hh", @[System::StartTime]), 2) + "-"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", @[System::StartTime]), 2) + "-"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", @[System::StartTime]), 2) + ".txt"

This will evaluate to a filename like MyOutputFilePrefix_YYYY-MM-DD-HH-MM-SS.txt, as requested above. [2]

Set the EvaluateAsExpression property of the FilePath variable to True and set the Expression property to this expression:

@[User::FolderName] + @[User::FileName]
At this point we have a variable, FilePath, that will always evaluate to a fully qualified file path that is dynamically updated to reflect the time the package started executing. This is exactly what we need, right? So the one thing that remains to be done is to use this value to create the files being exported by the data flow. And to do this we just need to update the Flat File Connection Manager to base its ConnectionString property on this expression:

@[User::FilePath]
Then, run the package a few times, and take a look in the output folder:

Pretty simple, eh? Once we had the base package (the starting point for the whole exercise) set up, it was simply a matter of setting a few property expressions to make the whole thing work as needed.

It's also worth noting that the technique where there are three variables to contain the file path is completely optional as well - you could just as easily build a single (although admittedly more complex) expression for the ConnectionString property, and skip the variables entirely. I personally prefer having the variables to contain the intermediate values that go into the file path because in most situations there are opportunities for reuse of these intermediate values, and having them stored in variables make that reuse very simple.

And of course, you can easily update the FileName expression as needed to get the file name you require. The SQLIS.com wiki has a page dedicated to date expressions, and I will generally start there whenever I need to write a date expression myself. In fact, you'll probably notice that the expression I used for the FileName variable was blatantly stolen (and then modified just a little) from one of the examples on the SQLIS wiki site.

So once again we've witnessed the power of expressions in SSIS. By setting just a few properties to evaluate as expressions, we've quickly and easily added dynamic functionality to a package. What could be better?

[1] Since the purpose of this post is to focus on the dynamic file name, I'm being deliberately vague here. This is stuff that you should know already, so if you don't, check here: New to SSIS- Start Here!

[2] Although this example is using the @[System::StartTime] variable, you can use any date variable (or function, like GETDATE) for your expressions - use the one that makes the most sense for you.

BIDS Helper and SSIS Deployment

2008-06-25T12:16:00.001-07:00

Back in November I posted about the 1.2 release of the amazing BIDS Helper project on CodePlex and the new SSIS-related features it included. Well, the folks who contribute to the BIDS Helper project have been hard at work getting ready for their 1.3 release, and today they checked into TFS the first set of features designed to provide real-world deployment capabilities from within the Visual Studio (BIDS) development environment.

Now, not only can you create the standard deployment manifest[1] you can also deploy automatically and (here's the exciting part!) have BIDS Helper automatically create a batch file that calls DTUTIL each time you build your project.

How cool is that!

The reason I'm so excited about low-tech batch files is that they are incredibly simple to integrate into any deployment process, and they're completely hands-free. They may not be sexy, but they're close to perfect nonetheless.

Take a look here for more information: http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Deploy SSIS Packages

The bad news is that version 1.3 is not yet ready for release, so if you want to start taking advantage of this functionality you will need to download the BIDS Helper source code and compile it yourself. Which I am now about to do...

[1] Which seems like a good start, but quickly becomes a dead-end, because it is dependent on a user clicking and typing every time it is used, which makes it very undesirable in a real-world deployment scenario.

SSIS and SQL Server Agent - Choosing the Right Job Step Type

2008-06-25T05:27:00.001-07:00

My "SSIS and SQL Server Agent" post is the most frequently accessed post on my blog, or at least it has been for the two months that I've actually been tracking site usage. But some recent posts on the SSIS Forums on MSDN have made me realize that it was probably long overdue for a follow-up focused choosing the correct job step type.

Microsoft has provided two different SQL Server Agent job step types that you can use to execute SQL Server Integration Services Packages:

The "SQL Server Integration Services Package" job step type
The "Operating system (CmdExec)" job step type.

Now which one leaps out at you as being ideal for running SQL Server Integration Services packages?

Unfortunately, despite the name and the oh-so-friendly GUI through which you can specify all sorts of options, the SQL Server Integration Services Package job step type is not the one you want to use. There are quite a few reasons I believe this (and it's worth pointing out that while there are people who agree with me, there are some very talented SSIS people out there who do not) including:

Consistency: You can create a batch file that calls DTEXEC, test that execution outside of SQL Server Agent, and then copy and paste the DTEXEC command line into the SQL Server Agent job. This minimizes the number of environmental variables[1] that change between testing package execution outside of SQL Server Agent and testing package execution within a SQL Server Agent job.
Discoverability: I realize that this won't apply to everyone, but I personally find it much simpler to look at command line switches and options than to look at nine different tabs in a tabbed window. Looking at the command line I know that I'm seeing everything. Looking through the tabs, I'm pretty sure I'm going to miss something.
Troubleshooting and Error Output: This is the big one. When you run a package through the SQL Server Integration Services Package job step type, you're limited to the error logging that is already configured within the package itself, and this isn't always sufficient to diagnose and resolve problems with the job. But when you execute a package through DTEXEX and the the CmdExec job step type, you have more options that make tracking down and resolving problems much simpler.

So how do you do it? And do you really want to, if you're not one of those weird people who would rather have a command line than a GUI?

Well, the answer to the second question here is yes. Yes you do. Because even though you're using the CmdExec job step type, you still have a GUI to use. And here's how.

Double-click on the package you want to run.[2] This will open up DTEXECUI, the Execute Package Utility.
In DTEXECUI, specify all of the options that you need by using its nice friendly GUI.
On the Command Line tab in DTEXECUI, highlight and copy the Command line text - these are the switches and options that need to be specified for DTEXEC to duplicate the options you selected in the GUI:
In you SQL Server Agent job, add a new job step, and specify the job step type as CmdExec. In the Command text box, type DTEXEC and a space, and then paste the command line text that you copied from DTEXECUI earlier[3]:
On the advanced tab of the New Job Step dialog box, enter a file path and name in the Output File text box. (This is the big thing.) With this specified, the output from DTEXEC, which is exactly what you want when troubleshooting problems, will be written to a text file. You can also specify whether the file should be overwritten or appended to with each execution.

Now, when you execute your SQL Server Agent job, all DTEXEC output will be written to this text file. And if something goes wrong, you now have a single, easy to use location for tracking down and resolving the problems.

One of the most common things that the MVPs and other people who help out on the SSIS forums request is "the complete set of error messages and warnings from the package execution."[4] The reason we ask for this is that it's pretty much impossible to remotely troubleshoot someone else's problems without complete and explicit output. And the greatest thing about using the CmdExec job step type is that all of this information is now in one place. Copy and paste the errors and warnings, and help is (hopefully) on your way.

[1] Not Windows environment variables - I'm talking here about "things that are important to your deployment environment."

[2] If your package is stored in SQL Server and not on the file system, you can still use this basic technique, but you will have to browse to and select the package from within DTEXECUI, as opposed to double-clicking on the package on the file system.

[3] You can also specify the fully qualified path to DTEXEC.EXE - this is helpful if DTEXEC is not on the path, or if you're on a 64-bit server and want to explicitly select the 32- or 64-bit version of DTEXEC for this package.

[4] And we're not the only ones who ask for this. Your in-house support folks will probably ask for the same thing if you have a support team who works with SSIS.

File Name Expressions

2008-06-23T12:45:00.001-07:00

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.

BI for the Virtual Guy

2008-06-20T10:40:00.002-07:00

Yeah, I know I'm stretching for the title of this post, but my allergies are killing me today and this is really the best I can do at the moment.

So what's this "Virtual" bit all about. It's about the first-ever SSWUG Virtual Business Intelligence Conference, coming this September to a computer near you. I'm in the process of helping to organize the conference and I've been having a blast. We have quite a few great BI speakers lined up and are in the process of finalizing the lists of speakers and sessions.[1]

But why do I mention this today?

SSWUG is about to kick off its second SQL Server Virtual Conference next week - it's being held from June 24 to June 26. And I'm excited that I get to attend. The reason I'm excited about attending is the very cool format of the conference - check this out:

http://www.vconferenceonline.com/sswug/demo.asp

The virtual conference format pulls together the best of "real" in-person conferences, along with the best of an online learning experience. The video on the page above shows footage from the first SQL Server Virtual Conference[2] and although the video has catchier music than you're likely to get during the actual sessions, it gives you a great taste of what the virtual conference format has to offer, such as :

High-definition video
High-quality audio
Real-life views of the speaker, the slides and the demos
Everything delivered online - no need to leave your desk

And there are some things that don't come through in the sample video, but you also get:

Interactive online chats and Q&A with the speakers
Downloadable PowerPoint slide decks
Downloadable demo files

But what is most exciting to me is that on top of all of these things, the virtual conference is flexible. The conference is three days long, and there are three sessions going on at any given time. At a traditional conference you would need to decide which session to attend, and the other two would be unavailable. Here, each day's content is repeated three times throughout the day, so if you don't catch a session the first time around, you can go back and view it later, so you have the flexibility you normally only get with online content and the interactivity (remember the Q&A with the speakers?) that you normally only get with an in-person conference. And to make things even better, if none of the primary times work for you (or if you get called into emergency meetings, as is often the case in IT) you also have the option to view any of the conference sessions on demand for the two weeks following the conference.

When I was first contacted about participating in the Virtual Business Intelligence Conference, I was more than a little skeptical. I pictured something like a ReadyTalk or Go To Meeting, with poor quality audio and video, and few options for interacting with the speakers and with other attendees. Instead, I found a platform that really seems to bring together the best of the physical and virtual formats, and I was delighted to get involved.

If this sounds interesting to you too, please stay tuned. I'll post more information over the weeks ahead, as we move closer to the conference dates from September 24 to September 26. So if you have any questions, please let me know, and please spread the word as well. There are a lot of people out there who can benefit from this content, and not everyone can make the trip to Orlando for TechEd or to Seattle for the Microsoft BI conference, so please help let them know that there is another option out there.

UPDATE 24 June, 2008:

It always weirds me out when I learn that people actually read my blog. I get enough information from Google Analytics to know that I get a decent number of hits each day, but that's not the same as getting an email from a real person about a post.

Like the email that I got today.

This morning I received an email from the account manager responsible for the conferencing solution that I use for work, wanting to know how she could work with me to resolve those problems. (How's that for great customer service? ;-) Reading back through this post I can see how it could be interpreted as "these conferencing systems are bad" as opposed to the "these conferencing systems are not ideal for the virtual conference format" message that I had intended. Here is the relevant part of my response to her, hopefully to eliminate the negativity that slipped into the post.

I didn’t mean to imply that I had any issues with ReadyTalk, LiveMeeting or GoTo Meeting for their stated purposes; simply that I don’t believe that they are ideal for the virtual conference format. The issues that I see are:

· Attendee join/leave – Most conferencing systems will announce each attendee by name and/or with a beep when the attendee joins or leaves the conference. For a conference call this functionality makes sense, but for a virtual conference this would be very disruptive.
· Audio Quality – The quality of the presenter audio in a conference call is dependent on the quality of the presenter’s phone line and the background noise in the presenter’s location, usually a home or office. Obviously this isn’t a problem with the conferencing system, but for a virtual conference it would negatively impact the attendee experience when compared to audio recorded with a professional microphone in a studio and then tweaked as needed before broadcast.
· Time-bound interaction – In a conference call, the only time that attendees can chat is during the session itself, and because the presenter is running the slides and/or demos, the attendees are limited to asking questions to (and getting answers from) helpers other than the presenter. Although the video may be recorded and made available for on-demand viewing, this is a unidirectional viewing; there are no options available for participants to interact. Again, this makes sense for a conference call, but for a virtual conference, it would be ideal to have the core presentation (slides, demos and presenter) and the attendee interaction/chat available independently, enabling the speaker to answer questions while the recorded session is being viewed.

I hope this helps clear up any confusion I may have caused!

[1] I know that quite a few strong BI speakers read this blog - if you're interested in being considered as a speaker, feel free to post a comment or send me an email over the next few days.

[2] Which was a huge success, which is why SSWUG is organizing another one and branching out to cover BI, SharePoint and .NET as well.

Splitting a Delimited Column in SSIS

2008-06-20T04:48:00.001-07:00

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:

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.
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.
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.
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.
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.
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.
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

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.
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

2008-06-19T07:10:00.001-07:00

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.

SSIS Excels - But Not at Excel

2008-06-18T12:12:00.001-07:00

In the two months since I began using Google Analytics to track visitors to this blog, I've noticed a few things:

Two of the top three articles on the blog are related to working with Excel.
26 of the top 100 searches that referred users to the blog had to do with Excel in one way or another.
SSIS doesn't deal with Excel data particularly well.

Ok, I'd actually noticed that last one long before I started using Google Analytics. And you've probably noticed it too.

I've long considered posting more about using SSIS and Excel, but I haven't, primarily because it's not something I deal with regularly in the real world, and because it's too annoying an exercise for me to go wading into it when I don't really have to.

And yes, I suppose that's another way of saying that I don't really love you enough to do the work.

But fortunately, Douglas Laudenschlager[1] does love you - he loves you more than you know. How do I know this? Because he is writing a three-part series of blog posts about how to work with Excel data in SSIS. Check this out:

These articles present great information about how Excel manages data, what connectivity options are available and how to effectively use those options from within SSIS. I honestly don't know if there is anything in them that is not available elsewhere, but I can say with certainty that there is no other single location that covers all of the information that Douglas has gathered into these posts.

Please do yourself a favor and read Douglas' posts. If you deal with Excel data in your SSIS packages, you owe it to yourself. I know that the next time I need to wrestle with Excel in SSIS, I'm going to re-read all three posts before I spin up Visual Studio...

UPDATE: As of 21 June 2008, Douglas has posted the third and final installment in this series, and the link has been added above. I love the fact that he starts off with "The Root of All Evil." That's Excel in a nutshell...

[1] In case you haven't been paying attention, Douglas is a technical writer on the SSIS documentation team. He started blogging back in late April 2008, but has been consistently posting such excellent technical content that his blog has been on my "must read" list ever since.

It's Not Easy Being BI

2008-06-18T09:54:00.001-07:00

I've been known to sometimes say that "being BI effectively doubles your chances of getting a consulting engagement on a Friday night." Yeah, it's kind of cheesy, but if you can't have fun with wordplays, what can you do, right?[1]

Well, the joke is sounding a little less funny today.

I'm currently involved in organizing a business intelligence conference[2] and have been sending emails to colleagues and potential speakers and have not been having the best of luck. In fact, when sending one email to an employee of a certain software company located in the Pacific Northwest, I got this explanation for why the email was rejected:

<Your e-mail was rejected by an anti-spam content filter on gateway (1.2.3.4). Reasons for rejection may be: obscene language, graphics, or spam-like characteristics. Removing these may let the e-mail through the filter.>

Yeah, no kidding.

I then went through the email and replaced all instances of "BI" with "Business Intelligence" and the email went through without error. But today I've been fighting with a few more similar problems - what a way to waste a few hours I didn't have to spare.

Has anyone else seen this issue?

[1] There are many other humorous stories that revolve around this particular play on words, like the well-known Microsoft speaker who wore his "BI Power Hour" t-shirt to the hotel bar at TechEd last year (not knowing that there was a gay pride event going on at the same time) and ended up having lots of nice men buy him drinks.

[2] More details on this in the days and weeks ahead - stay tuned!

Let the Blog Times Roll

2008-06-18T04:35:00.001-07:00

Just as a quick note, if you read my blog at http://bi-polar23.blogspot.com/ and not through an RSS reader, you'll notice something new today. I've added a blog roll with links to those SSIS-related (or vaguely SSIS-related) blogs to which I subscribe myself, and the most recent post on each blog. If you're looking for more SSIS goodness, check them out.

And while I'm blogging about blogging[1] anyway, let me share a pet peeve with you. What is it with people who configure their blogs' RSS feeds to only include the first few lines of each post? This drives me crazy - I like being able to go into Outlook (my RSS reader of choice) and actually read the new posts. Instead, I have to go into Outlook, read what little text is actually there, and then click to open up the rest of the article in a web browser. What's up with that? I know it's not a big deal, but it's a little annoyance that takes much of the joy out of there being a new post on an interesting blog. You guys know who you are - are you doing this on purpose?

[1] Or metablogging, because I never metablog I didn't like...

Did you Miss PacMan?

2008-06-17T13:49:00.001-07:00

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:

Get the PacMan code from CodePlex and open it in Visual Studio.
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.
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.

SSIS on 64-Bit Windows

2008-06-14T17:06:00.001-07:00

I've just returned home from the TechEd 2008 conference in Orlando - what an amazing two weeks this was. Even though the conference ended only yesterday, it already seems vaguely unreal, as if it were too much fun to have been real.[1]

But while I was having fun at TechEd, members of the SSIS team were hard at work[2] writing about one of my favorite topics: SSIS deployment. And since 64-bit deployments was such a large portion of my second breakout session, it seems like synchronicity[4] that both Douglas Laudenschlager and Matt Masson both blogged on the topic as well in the last few days. Check here to see Douglas' excellent post about considerations when using SSIS on 64-bit machines, and here to see Matt's follow-up about how the SQL Server Integration Services job step type in SQL Server Agent (just introduced in SQL Server 2008 RCO!) now provides the option to use the 32-bit SSIS runtime. That's pretty cool. I doubt I'll be using this and giving up DTEXEC any time soon, but it's a good step forward for 64-bit deployments.

Side note: Expect another post or two about SSIS deployment in the next few days. I realized on the flight home that I neglected to mention a few important considerations during that breakout session, so once I've had the time to remind my family who I am, I'll fill in the gaps here. Stay tuned!

[1] Although I am pretty sure it really was real. Otherwise I'll need to come up with an alternate theory about where all these t-shirts came from.

[2] Now I know why I didn't see these guys at the show...

[3] BIN-450: SQL Server Integration Services Deployment Best Practices

[4] Or perhaps some other Police song

SSIS, SQL Server Agent and WMI

2008-06-11T14:58:00.001-07:00

I need to hang out with Donald Farmer more often. He knows more about SSIS than I will ever forget - or something like that.

Earlier today I co-presented another interactive theater session (largely a repeat of the session we did last week during the developer conference, but with some new content and a different vibe) with Donald and (as before) I learned a few things about using SSIS that I had never seen before. One of them has to do with one of the most popular[1] SSIS topics: SSIS and SQL Server Agent.

This lesson doesn't actually have much to do with SSIS - it has to do with how you trigger the execution of a SQL Server Agent Job. Normally SQL Server Agent Jobs are executed on a schedule, so that the same packages run at the same time every day. To be honest, this is the only way I have ever used SQL Server Agent. But (as I learned today) you can also configure your SQL Server Agent Jobs to be triggered by Windows Management Instrumentation (WMI) events as well.

What does this mean?

It means that any events that are raised by the operating system (or other software that uses WMI) can be used to trigger the execution of your job and your packages. Think about having packages that automatically run when your processor utilization drops below a specific threshold as an example. The possibilities are endless[2] and the setup is documented in SQL Server Books Online here: http://technet.microsoft.com/en-us/library/ms366332.aspx

Enjoy!

[1] At least if the web traffic reports for my blog are to be believed.

[2] This is a nice way of saying that I don't know enough about WMI to come up with more examples without making a fool of myself.

Script Tasks as Execution Placeholders

2008-06-11T08:30:00.001-07:00

I've blogged previously[1] about a technique that involves using the Script Task in SSIS as an "execution placeholder" - essentially just an "empty" task that doesn't do any work, but which gives you something from which to connect a precedence constraint with an expression, enabling dynamic execution logic downstream. All in all it works pretty well, since the Script Task in its default state returns Success and doesn't actually do anything. It's simple, and it works.

Or so I thought.

If you take a look at the comments for the post referenced above, you'll see that Bart Duncan asked if there was an advantage to using the Script Task as opposed to an empty Sequence container. My response was basically that either one should work fine. Well, this is the case for SSIS in either SQL Server 2005 or SQL Server 2008, but apparently this is not the case for upgrading packages from 2005 to 2008. I have one package in particular (it's a "master controller" package that is responsible for orchestrating the execution of lots of other packages, so it has a lot more control flow logic than I would usually put in one package) and when I recently did a test upgrade[2], I got a bunch of errors. In essence, they all say this:

Error at Script Task Name [Script Task Name]: No entry point was found during the migration of "ScriptTask_91682d2bfda94fdcb87f72f4c97cd852". Add an entry point to ensure that the script runs.

Apparently (please note that this is an educated guess, not something that I have researched and validated in depth) when you add a Script Task to the SSIS 2005 control flow design surface, the designer does not call out to Visual Studio for Applications (VSA) to precompile the code. This is OK in 2005, because the VSA runtime knows how to handle these "uninitialized" Script tasks, but when upgrading to SSIS 2008, which uses Visual Studio Tools for Applications (VSTA) instead of VSA, the package upgrade wizard and/or VSTA don't know how to handle this previously-valid scenario.

So I guess I owe Bart an apology, eh?

In any event, there are two simple workarounds that you can perform with your SQL Server 2005 SSIS packages that use this technique:

Replace each "empty" Script Task with an empty Sequence Container. This will eliminate the problem altogether as it eliminates the use of Script tasks entirely.
or
Open each "empty" Script Task in the VSA editor and then close it back out again, clicking OK to save changes. This will eliminate the problem because it gets the Script tasks into a state that the upgrade wizard knows how to handle.

At first glance, option 1 probably looks more attractive, but it is a little more complicated than that. Remember: the whole reason we're having this discussion in the first place is because we needed a Control Flow component from which we could drag a precedence constraint with an expression. The problem that this introduces is that if we delete a Script Task in order to replace it with a Sequence Container, the precedence constraint will be deleted as well. Recreating the precedence constraint is simple enough, but then you would need to manually edit each affected constraint to recreate the expression as well. In my "master controller" package I had dozens of these constraints, so manually rebuilding everything didn't have much appeal - I went for option 2.

[1] Well, I guess I've been busy. When I looked up that previous post so I could link to it, I was surprised to discover that it was from May of 2007. I didn't realize that I'd reached my one year blogging anniversary already. How time flies when you're having fun...

[2] It's worth noting that this upgrade was performed using the February CTP of SQL Server 2008; I have yet to make the time to get RC0 installed, as I am somewhat tied up at TechEd and do not have a machine to sacrifice this week.

Master Data Management

2008-06-10T13:14:00.001-07:00

I attended this morning an Interactive Theater session with Rajesh Patel (Program Manager) and John McAllister (Principal Program Manager) from the Master Data Management (MDM) team at Microsoft. I've been following MDM on and off for the last year or so, after I got to see Roger Wolter present on MDM at TechEd 2007, but there hasn't been a lot of news to follow. So when I saw this session on the schedule I knew I had to attend.

The session itself was great - John and Rajesh came in with a slide deck and an agenda, but when they learned (following a show of hands from the audience) that the people in attendance wanted something different, they basically threw away the slides and gave us exactly what we asked for, and it was lots of fun.

Unfortunately, there wasn't a lot of new information that I hadn't heard before (although it was great to hear it again from the people who are building the software), but there were a few gems that I picked up:

Microsoft MDM will ship as part of Microsoft Office SharePoint Server (MOSS) in the next release of Office - Office 14. It will be a shared service in MOSS and the admin and data steward UI will be presented through the MOSS portal.
The MDM team is definitely thinking about the synergy between MDM and BI. I didn't come away from the session with a really clear story in my mind about how (for example) an SSAS dimension would relate to an MDM hierarchy, but I attribute that vagueness to the poor acoustics rather than to a vague vision on the part of the presenters.
There is a technology preview version of MDM that is available today. You can email mdmvibe@microsoft.com and request access to the software. The bits you will get will be the same Stratature software that Microsoft acquired a year or so ago, but if you want to start working to see how MDM will fit into your BI solution or into your enterprise, it's a great opportunity to get a head start.

Now I wonder what other tidbits I'll pick up this week...

Auto Connect: Another SSIS Lesson Learned

2008-06-10T12:48:00.001-07:00

As I mentioned in a recent post, I had the pleasure to present with Donald Farmer at the TechEd Developers conference last week. It was a lot of fun, and I learned a few things during the 75 minute session. One of them you've already seen, but here's another one - SSIS Designer Options in Visual Studio. This isn't quite as interesting as the ability to share a lookup cache, but it's still always nice to learn something new about a familiar tool. Here's the deal:

In Visual Studio, select Options from the Tools menu. In the Options Dialog, expand out the Business intelligence Designer node and then the Integration Services Designers tabs to get to the interesting stuff.

The General tab has a few options - the two that are most interesting for me are the "Show precedence constraint labels" option and the "Script Language" option:

The "Show precedence constraint labels" option will, when turned on, automatically display the constraint option (Success, Completion or Failure) on the control flow design surface for each constraint. I personally think that this is a good idea, but it would be a great idea if it displayed the constraint option and the constraint expression (as if the ShowAnnotation property were set to ConstraintOptions) since this is something that cannot be conveyed through the color of the constraint arrow.

But (oddly enough, given the setup at the top of this post) this isn't what I learned during the TechEd session last week. This is just something that I stumbled across while trying to get screen shots for the other two options below. The really cool stuff I learned is on the Control Flow Auto Connect and Data Flow Auto Connect tabs:

Basically what these options (click on the images to see more detail) give you is the ability to have your control flow tasks or data flow components automatically connected without your needing to drag the arrows to connect them. You can simply enable the auto-connect feature, specify the direction (right, left, above or below) in which the new component should be added (and for control flow, the type of precedence constraint to add) and then when you double-click on a toolbox item, it will be automatically added to your design surface and hooked up (based on the settings you specify here) to the component you have selected.

Pretty cool, eh?

And there's even better news! Based on the wonderful feedback we received following our session last week (well, I'm sure the fact that I asked nicely helped too) the repeat session at the TechEd IT Pro conference this week will feature both Donald Farmer and myself. I'll be sure to let him do all of the hard work like he did last week, but everyone had a great time last week and I'm sure this week will be even better. Please be sure to meet us at 2:45 tomorrow afternoon in Green Interactive Theater 2 in the "big room" at the Orange County Convention Center.

SSIS and EBCDIC

2008-06-10T10:19:00.001-07:00

First and foremost, thanks to Douglas Laudenschlager for bringing this to my attention.

So with that out of the way, I'd like to point out that Amino Software have finally released their Lysine product, a custom SSIS component that enables the use of EBCDIC data in the SSIS data flow. Here's the blurb:

Lysine is an SSIS (SQL Server Integration Services) custom component that enables the inline conversion from EBCDIC (including redefines and packed decimal fields) into an ASCII byte stream to use within the pipeline in your SSIS package.

The major benefits of Lysine are two-fold:

It saves you from having to write a pre-processor to unpack and convert the binary data.

It prevents you from having to pre-process the data saving an extra, and many times costly, trip through the data.

Features

Conversion of EBCDIC Field Types

Comp3

Zoned

Redefines

Occurs

Occurs Depending On

Realtime preview of data as file definition is setup in an intuitive UI

Inline processing of data -- a single pass through the data.

Now normally I would not simply copy and paste a software vendor's marketing blurb into my blog - not without getting some sort of kickback anyway - but since so far as I know there is no other software on the market that solves this problem, and since this problem seems to come up pretty often, I thought that this was a good time to make an exception. It's not free (and for $1,499 some might argue that it's not cheap either) but if you need to convert EBCDIC data to ASCII data, odd are it will pay for itself pretty quickly. I haven't had the time to run it through its paces (and with a little luck I won't see any EBCDIC in the near future) but if you're struggling with this pain today you might want to check it out. Enjoy!

Luncheon, Redux

2008-06-10T07:52:00.001-07:00

Even though TechEd has now moved on into its second week, and I am hard at work preparing for my two breakout sessions on Thursday and Friday (I've actually been "ready" for weeks, but when I have a big presentation coming up, I can't help but obsess about it) I wanted to share one more post about one of the many highlights from the first week of the conference. The folks at Microsoft who organized the delightful Influencers Roundtable Luncheon last week have sent out the group photos that were taken at the beginning of the event. Take a look:

And to sort of round out this whole event, S. Somasegar has also blogged about his impressions of the luncheon. It is fascinating to me that he came away with the same impression from the luncheon that I did:

"I left feeling somewhat overwhelmed by the passion that people in the room had for a single topic that took up the majority of the conversation. The conversation centered around how we can better marry IT skills and expertise to non-profit work in the areas of healthcare and education. All of the conversations and comments focused on how each of the people in that room could step up and bring their expertise in technology and IT to help people that are less fortunate"

I've blogged on this in the past, although not recently, but this luncheon and Soma's blog post have both reminded me how vital it is that each of us take advantage of his or her own unique strengths to help others. Whether its something as simple as helping out at your local school or community center, or as involved as volunteering for the NetHope project, there is something that each of us can do to improve the lives of those around us. It's so easy to make a big difference with a small effort. And it's delightful to know that I'm not the only one who feels this way...

SQL Server 2008 RC0 Available

2008-06-09T14:44:00.001-07:00

The first release candidate build of SQL Server 2008 is now available for download on the MSDN and TechNet subscriber download sites. Unfortunately, this comes a little too late for me to rebuild my demo VMs for my breakout sessions here at TechEd later this week, but it's exciting news nonetheless. It's not surprising that a big interim release like this would take place during TechEd, but it is exciting even if it is not unexpected.

One of the interesting (and to me, unexpected) tidbits available with RC0 is information about new editions of SQL Server. Take a look here:

http://technet.microsoft.com/en-us/library/cc645993(SQL.100).aspx

In addition to the existing editions (Enterprise, Standard, Workgroup, Developer and Express) there are now also Web and Small Business editions coming in SQL Server 2008. The Small Business edition doesn't show up in the online version of BOL referenced above, but it does show up in the docs installed with RC0. I guess they're still finalizing things...

Sharing The Lookup Cache

2008-06-07T17:33:00.001-07:00

Sometimes I'm the last person to know. I like to think that I know at least a little about SQL Server Integration Services, but it still seems that I'm always discovering some technique or tidbit of knowledge that had previously escaped my notice. I learned one such lesson this week during the TechEd Developers conference in Orlando. I was co-presenting with Donald Farmer[1] during his "Microsoft SQL Server 2008 Integration Services: From D'oh to Wow" session Friday morning. Here's the deal:

in the SSIS data flow (both 2005 and 2008) the in-memory cache for a fully-cached Lookup Transformation can be shared between multiple Lookup Transformations, so long as they all use identical queries to populate their cache. This means that if two Lookups can be implemented with the same query (not a particularly common scenario, but it does happen from time to time) the performance of the package can be improved by only loading the lookup data into memory once.

There are opportunities here to refactor the lookup queries in existing data flows - consider a scenario where a data flow contains two Lookup Transformations, one to add a Department Name field to the data flow an done to and one to add a Department Contact field, both looking up based on the Department Key. A natural approach to solve this problem would be to have each Lookup use just the two columns it needs, thus making the lookup data as narrow as possible. (This is a best practice after all, right?) But by having both Lookups use the same three-column lookup query you can reduce the volume of data being read and cached, and potentially improve package performance. What's not to like?

Funny you should ask.

After the session was over, I went to visit my friend and colleague John Welch, who was staffing one of the SQL Server Business Intelligence booths at the conference. I explained to him what I'd just learned (I was all excited in being able to share this with someone who loves SSIS as much as I do) and asked him if he had every heard of such a feature. I'll paraphrase here, but his response was basically "uh, yeah." And John filled me in on the dark side of this cool little feature.

Take a look here: http://support.microsoft.com/kb/924016

That's right, there is a known bug in SSIS related to this behavior. Basically if there are multiple data flows in the same package, or in multiple packages that run in the same process, it is possible for the cache to be cleared before all of the Lookup transformations are done using it. There are some good workarounds (and some not-so-good ones too - I would probably not want to disable caching for my Lookups to work around this bug) included in the KB article above that can make this feature usable, but you need to be careful using it. And of course if you're using SQL Server 2008 SSIS, you can explicitly cache your data and share it in many more interesting and complex scenarios thanks to the new Cache Connection Manager options added to the Lookup transformation.

And of course this wasn't the only thing I learned yesterday, but it's the only one for which I have blogging time today. I've got the others filed away so once TechEd is done (maybe before, but as I have two breakout sessions for which to prepare this week I would not hope for too much) I'll have more SSIS goodies to blog.

[1] Donald was the Program Manager for SSIS during the SQL Server 2005 release, and is now the Principal Program Manager for SQL Server Data Mining. It was a great honor (and a ton of fun!) to share the spotlight with such an engaging, knowledgeable and funny speaker. And to make things even cooler, we're going to team up again for a repeat performance on Wednesday during the TechEd IT Professionals conference.

What A Day!

2008-06-04T13:44:00.002-07:00

I had the most incredible day yesterday. It was the first day of the big TechEd Developers 2008 conference in Orlando, but that wasn't the thing that had me the most excited. I was (and still am) excited because of my lunch date. So what was I so excited about?

My lunch date was Bill Gates.

No, I'm not kidding. Yes, it was really that cool.

So how did this come about? As you probably know, Microsoft has a wide variety of communities with which they interact, such as the MCT and MVP programs. Members of these programs, in one way or another, work with users of Microsoft software around the world, and Microsoft values and appreciates the work that members of these communities perform. Well, to make a long story short, I was nominated as a community "influencer' by some kind folks at Microsoft, and in the end was selected as part of a small group (around 15 people) to meet for lunch with Bill Gates and S. Somasegar (who owns the Developer Division at Microsoft) for an "Influencer Roundtable Luncheon."

[1]

The food was ok (I was hoping that there would be lots of foie gras and black truffles, but the chicken and veggies weren't too disappointing) but the conversation was great. Mr. Gates was totally relaxed and casual, and pretty funny too. For the most part the conversation revolved around ways that people (and companies) can make a difference in the world, which should be no surprise based on the makeup of the group.

And there were some very impressive people on the invitee list. One was Charles Hughes, a past president of the British Computer Society. He is working on a global organization to bring standards for professionalism and ethics to the IT world - something that is obviously very much needed. I had a chance to talk with him earlier in the morning, and he made a very strong impression. All of the things that he is doing are things that are dear to my heard, things that I do on a personal level and try to do locally, but he is working on a global scope, which is somewhat awe-inspiring. I don't know quite how I fit in with this august company, but I am delighted and honored to have been involved.

[1] Can you see the Manowar-shirt reflected in the photo above?

Stopping the Breakout!

2008-06-01T18:48:00.001-07:00

When people go to technical conferences, they tend to think along lines something like this:

"At 9:00 I'm going to go to this breakout session, and then at 10:30 I'm going to go to this other breakout session and then at 12:00 I'm going to go to that breakout session..."

Conference attendees seem to spend all of their time running from one room to another, just to see some "expert" talk about technologies that interest them. How crazy is that?

Wait a minute, you say! That's not crazy - that's the whole point of going to a technical conference, right?

Wrong!

The point of going to a technical conference is to have access to knowledge and resources that you can't get anywhere else. And if you're planning to attend Microsoft's flagship TechEd conference this week or next week (or both!) then the breakout sessions will all be included on your post-conference DVDs. That's right - Microsoft will mail you a set of DVDs with all of the sessions on them so you can watch them whenever you want to.

So what should you do while you're in Orlando? How about visiting the Technical Learning Center. The TLC is the place to be, if you ask me. This is where the experts will be hanging out, just waiting for people like you to come up and say hi and ask a question or two. These experts include Microsoft Certified Trainers, Microsoft Most Valuable Professionals, and quite a few members of the product groups that build the Microsoft tools we all love so much. And not only are there experts, but there are also whiteboards, tables and comfy couches and chairs, so you can relax in delightful comfort while you're basking in the inside knowledge that only the people who build the products can have.

Oh yeah, they give away the occasional t-shirt as well.

In any event, it's not too late to fine-tune your conference plans. It would probably be silly to not go to any breakout sessions at all (because you can always go up and talk to the speakers once the session is over) but make sure you take the time to visit the TLC. I promise you won't regret it.