The Vaguest Error Message I Ever Saw in Integration Services

There was this one time when I was working on a project and we were trying to load a datawarehouse.   I came in one morning to find a team member staring at a failed SSIS package with this error message at a Data Flow Task, specifically coming from the query in the ole db source:

[SRC_OLEDB ] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: “Microsoft SQL Server Native Client 10.0”  Hresult: 0x80004005  Description: “Internal Query Processor Error: The query processor encountered an unexpected error during execution.”.

Vague much?

Continue reading

Five Places to Document Before You Win the Lottery

When a project starts to go sideways and you’re cursing Naive Past You who signed off on the “aggressive” time estimates as you feed quarters into the vending machine in hopes that powdered donuts and honey buns won’t give you a heart attack before you hit the deadline, the last thing you want to talk about is documentation. Non-developers, if you value your life, observe the developer and her surroundings before you ask whether the documentation is updated. If you see any of the following…

  • Pyramid of Diet Coke or Mountain Dew cans
  • Pile of takeout containers (half-eaten because there’s no time)
  • Wild, bloodshot eyes
  • Caffeine-induced shakes
  • Sleeping bag and/or pillow

 …Back away slowly. DO NOT, under any circumstances, ask the developer if the documentation is up to date. You may lose a valuable body part before you can wedge yourself behind the file cabinet.

I love the beginning of a project. It’s full of resolutions and promises about how this time it’s going to be different. A quick run-down of statements I’ve heard (and said):

  1. “We’re building time into the schedule to keep the documentation up to date”
  2. “All of my time estimates will include some buffer for documentation”
  3. “QA will not sign off without documentation”
  4. “We’ve made accurate documentation part of the critical path”
  5. “We’ll document as we go”

What ends up happening? I can sum it up in 4 words: “We’ll fix it later”. But we never do. Then we leave and the next poor soul comes in blind and realizes that the documentation is either non-existent or woefully out of date. He gets to spend the next few weeks being indignant and self-righteous because the documentation is bad. He quickly forgets the undocumented tangle of code he left behind at his last job.

So, what exactly is documentation? Is it a necessary evil, a sacrifice to the development lifecycle gods? Is it yet another bit of unproductive administration work you have to do to check all the boxes on the project plan? Is it some antiquated legacy of waterfall methods? No. It is none of these things. Documentation, when done well, is a conversation between you and the next guy who comes along and looks at your work. You might not be there to explain why you had to do that weird thing in that stored procedure. You might not be able to tell the story behind the funky flat table you wish your name wasn’t associated with. You might not be able to sit down over lunch and talk about the wacky business rules you’re trying to enforce. But, your documentation will be there. More than anything else, this is your legacy.

Am I proposing that you create exhaustive documentation explaining every decision and line of code? No. That’s totally unrealistic, and I believe that it’s what gets us into trouble. Overwhelmed by the goal of perfect documentation, we end up not writing any documentation at all. I fall into the good enough camp in my approach. Even then, it can be a struggle to keep up. In that spirit, I have a few must-haves and guidelines for what and when to document. Also, I’m sort of a function over form kind of girl. If it works, do it. If it’s pretty, well, that’s nice too. Of course, I’m a developer. If you’re a DBA, your list will probably look different. But, I recommend the same basic approach. What are my must-haves and what are my triggers for a bit of explanatory prose? Or, you know, poetry… but if you have time to write documentation in iambic pentameter, you probably need a more challenging job.


Data Dictionary – If nothing else, get the basics down:

  1. Entities and their purpose
  2. Attributes and their definition
  3. Business names for entities and attributes
  4. Explanation or examples of what data can end up in a column

Source to Target Mapping – if you’re moving data around, make a map

  1. Where does the data come from?
  2. Where is it going?
  3. What are the stops along the way?

Anything Manual – if you have to do anything out of process or manually, write it down

  1. Bob from Accounting asked you to run a “quick little report” for him in 2009. Somehow, you’re still running it every Monday
  2. You manually kick off that pesky job every Thursday evening instead of scheduling it
  3. You had to (gasp!) manually update data in the database

Other Guidelines

In-Line Documentation – (Stored Procedures/Functions/Scripts/etc.)

  1. Set up a nice little template inside any procedure/function/script that tells: Author/Date/Purpose/Major Revisions
  2. If you did something unconventional inside some encapsulated code, make a note of why you went this direction
  3. If some piece of code started out pretty and then grew horns and a tail and bought a pitchfork from Evil Processes R Us, explain what happened
  4. If you’re working around some limitation of the application/database/etc., say how and why

In Case I Win the Lottery Documentation (aka, In Case I Get Hit by a Bus)

    Imagine that you walk out the door this afternoon, and for some wonderful (not getting hit by a bus) reason, you never step foot inside your office again. You never log into the network again, and no one knows how to get in touch with you. What do only you know? Trust me, if you’ve been with an organization for any length of time, I can guarantee that you have some proprietary knowledge. This can be the least formal of all. Just make a list of things you hope someone else knows how to do when you’re sitting in Tahiti sipping fruity drinks, ogling the very cute cabana boy as you ask him to bring you yet another extra towel.In our little SQL Server community, we talk a lot about being part of the community. (I know… totally Meta) Documentation is a great way to be a good citizen inside our world. Chances are someone you know, or someone who knows someone you know, will be coming into an organization after you leave. Helping the next guy ramp up more quickly and avoid your early stumbles is just about the best you can do to support your fellow database professional.