Getting Back What You Put In

Today was Christmas, and one of the gifts I received is a delightful book titled Making Artisan Chocolates by Andrew Garrison Shotts. I've spent the day with family and friends, so I haven't had much time to read it, but the author's dedication at the front of the book contains a quote I just could not wait to share:

"From my father, Don, I learned work ethic and discipline. He taught me that a worthwhile outcome is fully dependent on the time and effort it takes to get there."

It's obvious that Mr. Shotts' father was a wise and thoughtful man. It doesn't matter if you're a chocolatier, a data architect or a mechanic, the effort that you are willing to put into your work determines the quality that you get back out. For me this usually means spending those late nights researching how SSIS works under the hood, or how to eek just a little better performance out of a stored procedure (although now that I have this book I may well be spending some time dusting off my chocolate tempering skills) but no matter who you are and what you do, these are words truly to live by.

And now, those raspberry-wasabi chocolates look irresistible...

You Can't Go Home Again

Warning: This is a completely non-technical post. If you want to know anything about SQL Server or SSIS, please skip this one. If you want a little insight into my brain (please don't ask why you would want this - I have no idea) then feel free to read.

On my drive to my users group presentation in Rochester, NY two nights ago, I drove through my old home town. It was on the way and I wanted to stop by an old workplace to share some Christmas cookies I'd baked with some ex-coworkers.

No, that doesn't really cover it. I need to back up a little to do this justice.

Back before I started my career in IT, I worked for a regional retail drug store chain called Fay's Drugs. This was my first job; I started working there when I was 16 and stayed employed there for far too long. Although many people who know me today find this hard to believe, I used to be a seriously under-motivated person. I always pushed myself to do everything that I did as well as I could (these days arguing with the boss tends to get me a raise or a bonus; in those days it got me written up and yelled at, but that didn't stop me) but I wasn't particularly motivated to go out looking for bigger and better challenges like I seem to do today. I was comfortable where I was, and in those days I wasn't compelled to constantly push myself out of my comfort zone.

And Fay's was like a second home to me. Maybe even a first home, because for many years I spent more waking hours there than I spent at my real home. I made friends (and more than friends) and learned all sorts of valuable lessons. I've been known to say that the things that have made me successful as an IT consultant are the things I learned at my "dead-end retail job" and not what I learned in my computer science classes in college. And although I moved on many years ago, I have still kept in touch with many of the friends I left behind. And even though Fay's was bought out shortly after I moved on (it hasn't been called "Fay's" by anyone but me in well over 10 years) it was still home to some part of me.

And in the years that followed, I slowly developed this idealized vision of the "Fay's that never really was." Things truly were much simpler then (of course, when you make 1/10th of the money, you generally get a lot less job stress too) but my mind turned it into much more than it ever really was. When deadlines have loomed in my consulting work, I can tell when I am pushing myself too far, too hard, when I am too stressed for my own health, because I have dreams of working for Fay's again. It's sort of like a subconscious canary in my mental coal mine.

In short, silly though it may sound, I have had a lot of emotional investment in this long-lost part of my past.

So imagine my shock when I discovered that this part of my past was gone.

When I pulled up to deliver those cookies, I found that the store where I had been hired for my first job, where I worked for many years, where I learned so many valuable life lessons and made so many friends, had closed its doors forever. It was like a physical blow.

I couldn't dwell on it then - I had a 75-mile drive ahead of me still, and a presentation to deliver, and I could hardly do that if I were all mopey and distracted.

But this has been weighing heavily on me ever since. I've been down and depressed - not a lot, but enough that I felt compelled to write about it, that perhaps I could achieve some sort of catharsis by putting my pained thoughts into words. Who knows if it will work. I've heard the phrase "you can't go home again" many times in my life, but never until now has it really hit home quite like this...

Design Conflict

I had a dinner meeting a few weeks back with some members of my development team at work (including the new VP!) and one of the topics of the discussion was that of team dynamics. I've found over the years that the most successful teams are teams of "invested equals." Invested because people only give their best when they think that they can make a difference and when they know that they'll be rewarded, and equals because people (for some strange reason) tend to do what their bosses tell them to, as opposed to pushing back and demanding that their voice and their ideas be heard.

As part of this wonderful dinner conversation, I told a story of how Ted Malone (who still claims to not regret recruiting me) and I were both attempting to come up with a design for a vital new application component. He presented his idea. It was so wrong. I presented my delightfully well thought out idea. He failed to see its beauty. (Please keep in mind that Ted may have somewhat different recollections of this morning.) So we dueled, whiteboard markers at dawn, as it were, and by the time the dust had settled the resulting design was more complete, more elegant and more satisfying than anything either one of us could have come up with alone.

Which brings me to a quote:

"A design that comes out of an argument is always better than a design that comes out of a committee."

Believe it or not, this is actually my own quote. (I'm not usually nearly this pithy, and am forced to quote the brilliant people around me.) It's been sitting in the back of my brain for the last few weeks, waiting for a chance to come out again.

And that chance may well be on the horizon. I've been involved in an email discussion with our CTO (who may well have the biggest brain on the planet - this guy is scary sometimes) on how we may be able to apply Microsoft BI technologies to solve some very interesting (and by "interesting" I mean oppressively difficult) problems in the configuration analytics space. The opportunity I see ahead lies in the fact that I’m the type of person who needs a real, concrete problem to look at and to wrap my brain around. Then I can step back, generalize and come up with an abstract “problem domain” that represents the whole problem to be solved, of which my concrete example was only one instance. But I need that one concrete instance in order to begin.

Dennis, on the other hand, is the type of person who always thinks in abstracts. (Or in any event this is the impression I've gotten; I honestly haven't asked him. Yet.) He always has that overarching “big picture” in mind, and even though he can drill down into the little details at will, that’s not how he looks at the problems natively.

So Dennis and I have some whiteboard time scheduled for next month. My brain is almost literally salivating (yeah, picture that one) at the thought of the mental duel that lies ahead. Bring on the conflict!

SSIS and SQL Server Agent

SQL Server Integration Services is great. But SSIS deployment can sometimes be painful. One of the things that makes it painful is that SSIS is built on a relatively complex set of technologies (like SQL Server, Windows Server and Active Directory[1], for starters) and when you go to deploy your ETL solution, you need to have a pretty good understanding of how those technologies work - and how they work together - or else you could well be in for a rough ride.

Nowhere is this more evident than when it comes to deploying SSIS packages to run via a scheduled SQL Server Agent job. I wish I had a dollar for every time someone posted this message on the SSIS forums on MSDN:

"I built my package and it works great, but when I deploy it to SQL Server Agent it fails. Is this a bug in SSIS?"

Yes, I blogged on this back in May. But I've seen this same problem in one form or another at least a dozen times this week on the forums, so I wanted to post on the same topic again. It's important. And although no one part of it is really complex or difficult, there are enough parts that come together during deployment that a lot of people are really struggling with it, despite the volume of documentation and other information that's available online. Hopefully this post will help, at least a little.

Here's the deal [2]:

• When you run a program in Windows, that running process has your permissions. This is why Microsoft Word can save files to your user drive, when only you have permissions to access that folder. Each running process has the access permissions of the user account that launched it.
• When you run a program from a SQL Server Agent job, that running process has the permissions of the execution context configured for the job step. By default this is the SQL Server Agent service account, but it can also be a Proxy account, depending on how the job step was configured.
• By default, the SQL Server Agent service runs in the security context of the Local System account. This means that it has lots of privileges on the local computer, but none whatsoever on the network.

See the problem? Nine SSIS packages out of 10 need to access remote resources. These could be SQL Server databases (remember how we use Windows Authentication whenever we can?) or text files on a file server, or an Access database (MDB file) on a file server, or any number of other things. SSIS packages generally exist to move data from one place to another place, and generally those places aren't all on the same machine, so the packages need to execute in the security context of a user account that can access all of those resources on all of the machines involved.

Please let me repeat/rephrase that, because it's the really important thing in this post.

"SSIS packages need to execute in the security context of a user account that can access all resources on all of the machines involved in the package processing."

What does this mean to you?

It means that when you run the package yourself, the package has your permissions. It can do what you can do and access what you can access, for the simple reason that you ran it. And if you didn't have permission to access all of those resources when the project started, you discovered this problem early on, and got the access you needed because otherwise you couldn't have built the package at all in the first place.

So of course it runs for you!

But what about that SQL Server Agent job?

It should be pretty easy to figure out from here, right?

1. You need to create (or work with the people in charge of your network to get created for you) a Windows user account[3] that has all of the permissions necessary to access the resources your package uses, databases, file system folders, shared folders and so on.
2. Then you create a SQL Server Agent Proxy based on that Windows user account.
3. And then you tell SQL Server Agent to run your job step in the context of this proxy.

One, two, three, Go!

That's really how simple it is. And the craziest part of all? Microsoft has already documented the heck out of this online. Here are a few key links that will go into much more depth than I have here, so if you're still having problems, why not click on them and read what Microsoft has to say.

• From SQL Server Books Online, "How to Run a Package Using a SQL Server Agent Job": http://technet.microsoft.com/en-us/library/ms139805.aspx. (Please note that this page has links to sub-articles and related articles. Click on them and read those pages too.)
• From the Microsoft Support Knowledge Base, "An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step": http://support.microsoft.com/kb/918760. This article goes into many scenarios that could cause an SSIS package to fail when executed in a SQL Server Agent job, not just the security related stuff in this blog post, but from my experience, it's the security that is tripping up most people.

Are you ready to go? I sure hope so. Hopefully the next time someone runs into this problem I can simply point them to this URL instead of having to type up this information each time. And maybe, just maybe, someone will search and find this article before they cry for help on the forums.[4] And if that happens, it will all be worth it, and these blisters on my fingers won't seem to hurt at all anymore... ;-)

[1] No, SSIS isn't really "built on" AD, but read on and you'll see what I mean.
[2] Yes, I know I'm simplifying things quite a bit here, but that's intentional.
[3] If you have Active Directory, this needs to be a domain account. If not, then you have a bunch of reading to do on Windows security, because I'm not going there today.
[4] If you are that person, please post a comment to let me know.

Merry Christmas (of Steel!)

As regular readers of this blog know, I'm a huge fan of the heavy metal band Manowar.[1] Their music is incredibly powerful and is a constant source of energy and inspiration for me in many ways in my personal and professional life. I try not to let too much of this personal passion bleed over into this largely-technical blog, but in the spirit of the Christmas holidays, this time I could not resist.

Christmas holidays? Manowar? What am I talking about?

That's right - Manowar has released their first ever Christmas carol! They just made available both English and German versions of Silent Night for free download as part of a promotion with Amazon.de[2]. Please take a moment from your busy day and download one or both versions and see how Eric Adams' amazing voice delivers this classic Christmas song, and understand at least a little of how Manowar's music inspires me every day.

Merry Christmas!!

[1] You can find their official website at www.manowar.com, but it's not always work safe, and violates most UI and design guidelines known to man, so browse at your own risk.
[2] If you click on the link you'll see that the promotion is for the latest Manowar DVD, filmed at the Magic Circle Festival in Bad Arolsen, Germany this July. You should also definitely pick up the DVD as well, both to enjoy the music and to see if you can spot your favorite SSIS blogger on stage and in the front row. ;-)

Last Night's Deployment Slides Online

I had a great time last night in Rochester at the Visual Developers of Upstate New York users group meeting. I delivered a session on SSIS Deployment, based on requests from the organizer of the users group, who attended my ACM presentation back in October. Deployment is not one of SSIS's strongest points, so I knew we'd have a lot of fun. We had a great turnout and some killer questions, and everyone had a great time.

I've put the slide deck from last night's presentation online - you can get it here.

There's not a lot of "meat" in the slides (this is probably why I was actually able to finish on time, which is pretty much unheard of when I only have 90 minutes to work with) but quite a few people asked for them, so here they are. If you have any questions, comments or criticisms, please be sure to post them here.

Reminder: SSIS Deployment Presentation Tomorrow

In case you've forgotten to add this to your calendar, I am delivering a presentation on SSIS deployment at the Visual Developers of Upstate New York users group meeting tomorrow night. The meeting is scheduled to begin at 6:00 at the Microsoft offices in Rochester, NY. I've heard from the users group organizers that they have received many more RSVPs than they usually get for presentations this time of the year (it's hard to compete with Santa) so you may want to plan on showing up a little bit early too.

Don't forget - After the presentation there will be plenty of time for Q&A, so be certain to bring your tough SSIS questions!

Yay! PDC 2008 Dates Announced!

I'm a few days late in hearing this news, but my friend Anton let me know this morning. Consider it an early Christmas present (why do I have Elvis's "Blue Christmas going through my head now?) from Microsoft to you:

Save the Date!
Announcing PDC08
October 27–30, 2008
Pre-conference October 26, 2008
Los Angeles, California
OK, OK. We are delighted to announce the date and location of the next Microsoft Professional Developers Conference (PDC): October 27–30, 2008 at the Los Angeles Convention Center. PDC is the definitive Microsoft event for software developers and architects focused on the future of the Microsoft platform. Mark your calendars and save the date. More information coming soon.

http://msdn2.microsoft.com/en-us/events/bb288534.aspx

I'll be there - will you?

Books, books, books!

My "A Whirlwind Tour of SQL Server Integration Services" presentation last week at the CNY Developers User Group was a huge success. We had a big turnout and everyone seemed very enthusiastic about the capabilities of the SSIS platform, and many attendees stayed around very late to ask questions and get into wonderful tangents about SSIS.



And one of the most frequently asked questions was "what SSIS book should I buy?" Now in an ideal world this should be an easy question to answer (and in an ideal world I would have had a slide handy to answer it) but sadly, there is no single SSIS book that I can wholeheartedly recommend to developers who are new to the SSIS platform and tools. So instead of having a nice simple answer, I'm forced to resort[1] to answering with a list of links and explanations. And here is that list:

• Microsoft SQL Server 2005 Integration Services by Kirk Haselden: If you're new to SSIS, this is the one book to buy. It's not a great book (sorry Kirk, but I'm awfully picky) but the author has great insight into the product (as well he should, since he led the development team for SSIS up through RTM) and does a good job of communicating its ins and outs to the reader. If this book went into more depth and had more real-world examples (yes, and had twice as many pages) it would be a must-have.
• Professional SQL Server 2005 Integration Services by Brian Knight, Allan Mitchell, Darren Green, Douglas Hinson and a bunch of other people: This book fills in a few of the cracks that Kirk Haselden's book does not, but other than that it doesn't really deliver for me. If you have a book budget and need to buy more than one introductory SSIS book, get this one too, but otherwise you should probably pick up Kirk's book and move on...
• The Rational Guide to Extending SSIS 2005 with Script by Donald Farmer: This is the best resource I've found on using the Script Task and Script Component in SSIS, and is one of my favorite technical books in general. It's very well written with deep insight into the product, and is a very easy read. However, scripting is one of those "tools of last resort" for most SSIS projects, so this book is also not a "must have" if you're just getting started.
• The Microsoft Data Warehouse Toolkit by Joy Mundy, Warren Thornthwaite, and Ralph Kimball: This is probably the only "must read" book on my list, but it's not really a SSIS book, or a developer book, so I don't know if it's as appropriate for the people who asked for book recommendations as it is for me. This is a great "BI best practices" book, and while it has quite a bit of SSIS content, but it covers the whole gamut of real-world BI/DW projects from a "you need to do these things" perspective as opposed to a "this is how you do these things" perspective for SSIS, SSAS and SSRS with some other goodies thrown in as well.
• Foundations of SQL Server 2005 Business Intelligence by Lynn Langit: This book provides a great introduction and overview to the Microsoft BI stack (kind of like the last one, but thinner) and with a more hands-on, how-to focus. (I was the technical reviewer on this book, so I realize that I'm biased and will stop before I say too many glowing things about it. ;-)
• Expert SQL Server 2005 Integration Services by Brian Knight and Erik Veerman: This is my favorite SSIS book today, hands down. So why did I save it for last? As this title implies, this is not an introductory book - it assumes that the reader has a good foundation with SSIS fundamentals and is ready to start solving real-world problems more efficiently using tested tools and techniques. So this one is a "must have" but it may not be a "must have right now" for everyone.

Wow, it took me a long time to complete this post. Last month I averaged a post every day. Here it is now, halfway through December and I've barely managed to publish two semi-technical posts. (And it took me almost a week and half to type a list of books - how crazy is that?) Why is it that December is always the busiest month of the year for technical projects? It always seems to be that there is 50% more to do in December than any other month, despite the holidays. I've been swamped, and my blogging productivity is suffering, but at least I'm making all of my client-facing deadlines...

Anyway, enjoy! And let me know if you have any questions, too.

[1] Boy, it's great to have someone to blame! I never have nice simple answers anyway, but at least this time I can blame the SSIS book market!

Zoom, Baby, Zoom!

I know that this is likely to be the least technical SSIS blog entry you read today, but it's still useful, and no one else I've talked to seems to know that you can do it, so I'm going to blog on it anyway.

If you hold down the ctrl key on your keyboard and scroll the wheel on your mouse, you can zoom in and out of your SSIS control flow or data flow designer in Visual Studio. This is really handy for those extra-large packages when you need to see more than your monitors permit, and since the built-in designer tools have inconsistent support for zooming in control flow and data flow, it's both convenient and consistent.

Low tech, but cool and useful.

And to make this even more useful, you can use the same technique in just about any modern Windows application that supports zooming, including Internet Explorer and Word. Enjoy!