Christmas Came Late!

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

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

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

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

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

Math and Terror

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

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

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

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

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

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

(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

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

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

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!

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

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!

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

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.

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

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!

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

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:

• http://www.dafont.com/
• http://www.1001freefonts.com/
• http://www.1001fonts.com/
• http://betterfonts.com/

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

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

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?

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!

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!

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!

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

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

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

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

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

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

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

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?

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!

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"

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

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

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

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

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

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

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

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

[1] For example:

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

SQL Server Sample Install Epiphany

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

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

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

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

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

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

Oh.

Oh yeah.

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

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

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

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

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

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

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

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

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

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

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

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

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

Cake Wrecks

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!"

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

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

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

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

Visual Developers of Upstate New York

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

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

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

SSIS in Stockholm 2.0

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

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:

• WMV low quality video
• WMV high quality video
• MP4 high quality video
• MP3 audio only

Enjoy!

Building Packages Programmatically...

...just got easier.

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

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

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

The problem with this answer is threefold:

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

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

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

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

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

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

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

BIDS Helper 1.3 Release

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

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

Now.

SQL Server 2008 is Released to Manufacturing!

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

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:

• WMV low quality video
• WMV high quality video
• MP4 high quality video
• MP3 audio only

Enjoy!

SSIS Community Samples on CodePlex

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!

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

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.