SQL Server MVP Deep Dives

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

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

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

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

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

I’ll see you at PASS!

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

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

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

This is one of those “I could have sworn I blogged on this topic years ago” posts. I speak to this topic whenever I’m presenting on SSIS development best practices, and with my half-day mini-seminar in Stockholm last week[1] I was thinking of it – and speaking about it - again. Although SSIS is a great platform and provides pretty much all the tools you need to get the job done, sometimes it’s not always easy to know what tools to use and how to use them.

Which brings us, of course, to package templates. So before we continue, please read this Knowledge Base article on how to create a package template in BIDS.

Done? Good – let’s review, and paraphrase while we do it.

Package templates are pretty much the entire reuse story in SSIS.[2] You define the baseline package, including connection managers, log providers, configurations, variables and expressions, tasks, containers and precedence constraints and sometimes data flow components as well. When you create a new package you create a new copy of the template with this pre-defined starting point instead of starting with a blank package, so you can be sure that the package is just like the baseline you created.

In keeping with the theme established in the post title, this is the “good” side of SSIS package templates. They’re valuable, and when you’re building a large number of packages for the ETL subsystem of a DW/BI project, they’re invaluable, in that the common “plumbing” required by all packages can be defined once and used consistently everywhere. And this is particularly vital when working in a team environment, where different developers (each of whom inherently has his own style and own way of building packages) are working on the same project, which is typically the case on ETL projects of anything but the most trivial in scale.

Which brings us to the “bad” side of package templates, at least as implemented when following the guidance in the KB article referenced above. This guidance is firmly targeted at the lone developer; did you notice how all the instructions about creating a package and saving it in the Visual Studio install folder never mentioned on what computer you should be performing these steps? That’s because the article assumes the context is “your machine” – the lone developer’s machine or - at scale – on every developer’s machine.

Why is this bad, you ask? Surely it can’t be so much work to copy a DTSX file to the same folder on a few computers, can it?

Of course not. The “bad” side of following this approach comes not when creating the templates, but when the real world comes into play and the project continues to grow and evolve. In a perfect world, when the needs of the project are understood 100% before development begins, and the needs of the project never change after development begins, this approach will work just fine for team development. Your mileage may vary, but I’ve yet to work an SSIS project[3] like this.

In the real world, most successful BI projects are iterative and agile, just like most other successful development projects. If you look at SQL Server MVP John Welch or MVP and VSTS Ranger Ted Malone, their blogs focus strongly on agile BI. If you read Kimball, you will see an overarching theme of agile and iterative development. And if you’ve ever seen former SSIS program manager Michael Entin present on SSIS performance tuning[4] you’ve noticed this best practice under the heading of “Principles of Good Package Design”: “Follow Microsoft Development Guidelines - Iterative design, development & testing.”

Ok, so why did I just go off on that tangent and drop so many names? Regular readers will quickly note that my writing style practically revolves around tangents and will likely seize on this as the reason, but today it is to drive home a point: The real world is neither pretty nor neat, and people who know more about BI than I will ever forget[5] have gone out of their way to point this out.

Why is this bad, you ask yet again? Surely it can’t be so much work to copy a DTSX file to the same folder on a few computers, can it? And can you please just get to the point, Matthew?

Maybe. Picture this story:

A small team of five or six SSIS developers are working together on a project. The lead developer has created a package template for all developers on the team to use moving forward. He emails the DTSX file to each team member along with a reference to KB article 908018 for instructions on how to install and use the template.

A week later the lead developer identifies a problem with the package template.[6] He updates the template package and emails it out to his team with instructions to update their local machines with the new package right away.

Unfortunately, one of the team members is a “heads-down” kind of developer who turns off email during the day, and finishes a few more packages before he gets the message, even though he does save the new template package once he checks his email.

And another developer is on vacation when the email is sent, and by the time he gets back he has too many new messages in his inbox to ever get through, so he decides that if anything is really important then the sender will re-send it, and he just deletes anything that is unread to get a fresh start with no email backlog.[7] So he keeps on developing packages using the outdated template until the next update is sent out a few weeks later…

You see where this is going, right? This is gong to the “ugly” side of SSIS package templates. The ugly part comes in downstream when integration testing begins. The packages developed by the individual team members need to be deployed into a test environment and tested together, and of course at that point things start to fall apart. Everyone works extra hours and deadlines slip by while people try to track down problems they knew had been solved long ago. Although to be honest, the worst case scenario is that the test cases don’t find the problem at this point, and the ETL solution is deployed into production without the errors being resolved.

Unfortunately, I have seen this story play out in one way or another on several different projects over the years. The details are always different, but the ending is always the same: extra work, unhappy clients, and pain.

Now picture this story retold, with a few different details:

The lead developer works with his team to establish Team Foundation Server (TFS) source code control[8] before any development begins. He develops a set of packages that serve as “template examples” and checks them into TFS.

He also establishes standards with his team, so that each team member knows to Get Latest whenever starting work on a new package. This ensures that – no matter what happens – each developer always has the most recent versions of all packages with no need for “special case” tasks like updating a template.

The lead developer also instructs each development team member to create any new packages by right-clicking in Solution Explorer and selecting Add Existing Package, and then browsing to a current file from TFS.[9] This ensures that whenever a new package is created, it is being created as a new instance of a “rolling template” that is stored in TFS and as such the same version is always available for use by all developers.

Then, whenever problems are identified in existing packages, the “problem packages” are updated immediately, and the fixes are applied to any existing packages created from a now-outdated template.[10]

And whenever anyone goes on vacation, problems are found earlier, their impact is lessened, deadlines are met, and everyone lives happily ever after.

Ok, so the results are probably not so extreme, but in my experience on multiple projects this “rolling template” approach is the best practice way to approach SSIS package templates. Any attempt to utilize the Microsoft guidance in a team environment runs into problem after problem, and at the worst extreme I have seen a development team manually re-implement dozens of SSIS packages using a new template after churning for a full week while trying to reconcile and resolve the problems disseminated by using the “official” template approach.

And to be fair, the definition and implementation of policies and procedures can help make following the “official” approach less bad, but the starting point is inherently flawed for team development. If anyone were to suggest to me that following KB 908018 was the right way to approach package templates, I would have to politely[11] disagree.

What do you think? Even though I have been around the block a few times with SSIS, I don’t claim to know it all. I’d love to hear your thoughts on how you use package templates – especially if I took your name in vain earlier on - including what works and what does not work in your team environment.

 

[1] I never seem to find the time to do tourist stuff when traveling for work, but for some reason I just could not say no when I was invited to speak on SSIS best practices when in Sweden for a friend’s wedding. I have the world’s most understanding wife.

[2] I say “pretty much” because using small, modular packages and executing them via the Execute Package task or some other client, or using the SSIS .NET API to dynamically construct and execute packages are the other parts. In SQL Server 2008 and SQL Server 2008 there is not a lot to say about reuse in SSIS.

[3] Or projects using any other technology, for that matter. If you know of any projects like this and are looking for a consultant, I’m pretty sure I can work out a moonlighting arrangement with Microsoft. ;-)

[4] Or have seen me blatantly plagiarize his slide decks for my own talks.

[5] No Ted, this doesn’t include you. Sorry.

[6] I won’t go into the details of the problem because it isn’t really important to the story, but it could be anything along the lines of:

  a) A missing variable, which needed to be added.
  b) A variable defined at an incorrect scope, hiding a variable at the package scope when accessed from a Script task.
  c) A log provider needed to be added to support new project requirements.

You get the idea, right?

[7] Yes, people really do this. Yes, I’m thinking about doing it right now. I’ve been on vacation for a week and have been checking email at least once a day to respond to high priority items, but I still have nearly 700 unread messages in my inbox. ;-(

[8] Or Visual SourceSafe or pretty much any modern SCC system. But TFS has so many additional benefits, it’s hard to recommend anything else.

[9] Just to be clear, I’m not implying that there is any ability to browse TFS repositories in BIDS, but instead am saying that the developers all have the most recent packages from TFS and can browse to them on their local file systems.

[10] Odds are they use a tool like PacMan to batch update the existing packages. ;-)

[11] Or perhaps not so politely depending on the hour…

SSIS and the Package ProtectionLevel Property

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

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

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

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

Maybe not.

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

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

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

Why does this matter?

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

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

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

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

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

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

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

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

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

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

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

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

What about the other options? Let’s consider:

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

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

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

Maybe.

Maybe not.

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

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

We say “tough luck!”

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

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

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

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

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

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

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

 

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

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

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

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

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

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

[7] Awww….

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

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

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

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

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

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

SSIS Development and Source Code Control

In my personal best practices for SSIS development, I claim that we need to treat SSIS packages like code. When I give presentations on this topic, I have a slide that elaborates and talks about how we must use source code control for our SSIS projects just like we do[1] for our “traditional” VB and C# projects. It’s one of those irrefutable best practices of software development; you wouldn’t drive your car without your seat belt, and you’d never even think of building software without source code control.

In fact, this is so obvious and so important, I had assumed that I must have blogged about it years ago. So I came here tonight to get the URL so I could reference it somewhere else.

And I couldn’t find it. So here’s the post I was expecting to find,[2] so that the next time I look I will find it here.

The points that I like to bring up in my presentations are how even though SSIS development is software development, there are inherent differences in the tools that can turn into “gotchas” if you’re not careful. In particular, walk through these basic steps just to make sure you’re following me:

1. Open an SSIS package in Visual Studio – a package that has at least one user variable defined.
2. Look up at the tab at the top of the package designer. You’ll see that the tab displays the name of the DTSX file followed by the word [Design] in square brackets, in case you weren’t sure you were looking at the designer and not the raw XML.
3. Ensure that there is no “*” asterisk after the word [Design] – this asterisk shows that the file has been modified and needs to be changed, and for the purposes of this exercise we need it to not show up. If your package does show an asterisk, save it now to make the asterisk go away.
4. In the Variables window, click on the name of a variable. Don’t change anything, just click.
5. Then click on the design surface. Again, don’t change anything – just click.
6. Then look up at the tab. What do you see?
7. That’s right! An asterisk!! The package has been changed by a non-editing action on your part. Has the world gone mad?!?

Um… probably not. Or at least, not yet.

But let’s assume for the sake of argument that you followed this best practice and have been using source code control. And let’s further assume that since no one told you any different that you set up your source control plug-in in Visual Studio and left it with the default settings.

Uh-oh.

Let’s take a look at those default settings, shall we?

That’s right. The default settings are evil.

Why evil? Well, think back to the “not really editing a variable name” scenario from earlier. If this package were under source control, you just checked out that package without meaning to, and probably without realizing it. And that means that as soon as you walk out the door to go on that two-week vacation[3], you just became that guy. That’s right – the guy who gets blamed for things because he’s not there. Because the default source control settings in Visual Studio are to silently check out a file when the developer starts editing it. And while this may make sense for traditional code files, it definitely does not make sense for DTSX files.

And the worst part is, you really are to blame here, at least in part, because you did not resist, you did not fight back against the evil that consumed you and is now ravaging your team’s productivity. You’re kind of like Gollum.[4]

But do not despair – it is not too late to turn back from the path of evil! In fact, you really just need to change one drop-down, like so:

That’s right – just say “Prompt for exclusive checkouts” and the problem goes away[5]. Any time that you perform a step in the SSIS designer that would cause the package to get checked out (we walked through one with the variable name thing, but there are many more) you’ll be prompted to check out the file.

And that’s about it. As usual, I spent five pages to say what could have been done with one screen shot and ten words, but hopefully you’ll understand the context a little better this way.

So always use source code control, but also be certain that you have configured it correctly for SSIS.

[1] We do, don’t we?

[2] Yes, it’s late on a Saturday night, but hopefully you too will see that this is more than a little like time travel. Or is it just me?

[3] I did mention that you are going on vacation, right? Go on – you’ve earned it!

[4] Did I mention how much I loved you in those movies?

[5] Yes, this means that Sam and Frodo’s budding romance will probably never get a chance to bloom, but that is a price I’m willing to pay for you to enjoy your vacation.

Indexes and SSIS Data Flow Performance

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

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

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

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

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

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

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

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

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

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

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

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

So what’s the lesson?

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

The Danger of Thinking Grandly

There's a great article today on The Register - my favorite UK IT news web site. I love The Register because they cover so many facets of the IT world that I don't touch regularly myself[1], have a strong anti-Microsoft bias[2], and generally have a dry British sense of humor[3]. All in all, it's a good thing.

Today's article is on "Frameworks and the danger of a grand design" and should be considered required reading for anyone who writes code or builds applications. It's not rocket science, but it sums up quite nicely a bunch of lessons that I have learned through my own pain over the years. If you have also felt that pain yourself, you'll read the article and nod and smile. And if not, you may not quite get it, but next month or next year when you do feel that pain, you'll look back and say A-Ha!

So check out the article today: http://www.regdeveloper.co.uk/2008/05/29/grand_design_syndrome/

[1] Which keeps me informed on topics that I would otherwise know nothing about.

[2] Which keeps me honest, since I'm so firmly entrenched in the Microsoft world myself.

[3] Which keeps me amused.

Another Reason to Avoid Data Sources in SSIS

I've often railed on the evils of Data Sources - the .ds files that you can add to your SSIS projects in Visual Studio. I tell people to avoid them whenever I'm talking about SSIS deployment, and I even gave them a mention in my SSIS Best Practices blog post last year:

"Don’t use Data Sources: No, I don't mean data source components. I mean the .ds files that you can add to your SSIS projects in Visual Studio in the "Data Sources" node that is there in every SSIS project you create. Remember that Data Sources are not a feature of SSIS - they are a feature of Visual Studio, and this is a significant difference. Instead, use package configurations to store the connection string for the connection managers in your packages. This will be the best road forward for a smooth deployment story, whereas using Data Sources is a dead-end road. To nowhere."

And now there is even one more reason (as if there weren't enough already) to avoid them: they're going to complicate your package upgrade path from SQL Server 2005 to SQL Server 2008. This is due in part to the fact that the SQL Native Client provider name has changed from SQLNCLI to SQLNCLI10 in SQL Server 2008. The SSIS package upgrade wizard will take care of this in your packages and update your connection managers appropriately. But if you're using Data Sources in your project, they will not be changed, so when you next open your properly upgraded packages the new (and correct) connection strings will be overwritten with the old (and incorrect) values and the packages will fail.

And that's not what we want, is it?

So save yourself some pain and go with Package Configurations[1] instead, ok?

 

[1] In the context of full disclosure, I honestly don't know if the upgrade wizard is intelligent enough to find and fix connection strings in package configurations either, but they're still the better choice. ;-)

What's Best?

I was flying through Cincinnati yesterday and while waiting for my connecting flight I overheard (I wasn't trying to eavesdrop, but the guy was standing right next to me and talking loudly and clearly) a phone conversation about various aspects of IT consulting. During the conversation the person speaking next to me came out with this great quote:

"There's no such thing as best practices - only best practitioners."

Ok, I don't know if I really agree with this 100% (although I did smile and say "that's right!" to the man on the phone) but it is certainly an interesting idea that raises a whole set of interesting questions related to knowing what's "best" for a project.

Obviously, best practices exist, but they are generally just rules of thumb. They're general guidelines to follow in common situations, but they're not a panacea. In order for them to be applied to greatest effect, the person following the best practices needs to understand not just what to do, but also why.

In order to really get the best, you need not only best practices, but you also need the right people to implement them - the best practitioners, as it were. This is because context is practically everything when it comes to determining meaning, and in order to understand the context of a best practice recommendation, the practitioner needs to first understand two primary things:

1) The business context of the project - without understanding the real problems that need to be solved, no one can select an ideal solution.

2) The technology platform on which the solution will be implemented.

These two factors are vital when applying documented best practices because the best practices are generally presented as a set of prescriptive guidance - do this, don't do this, also do this - without a great deal of explanation. There's rarely[1] information about why a given step/task/configuration is considered "best." Because of this, the practitioner needs a solid understanding of how the technology platform works - this will let him interpret the best practice guidance and decide whether a given "best practice" recommendation applies to his current project context.

So what's best?

The answer of course is "it depends." The answer always depends on the context in which the question is asked.

But when you're looking for the best solution to your vexing problems, you need not only best practices, but you also need the right people to implement them. I've seen too many companies over the years who try to save money by only hiring "junior" personnel, but who then expect these inexperienced folks to deliver top-quality software on aggressive schedules. Although sometimes this can work, it's never repeatable and generally only succeeds through great personal sacrifice on the part of the team members.

The next time you're starting a project, think about the skills you're going to need, and realize that sometimes there's no substitute for the experience and deep knowledge of a "best practitioner."[2]

 

[1] Although this is beginning to change as technologies mature - the patterns & practices group at Microsoft does a great job here.

[2] No, I'm not trying to advertise or promote myself or anyone else. I'm booked solid (and then some) for as far as I can see, and don't have any ulterior motives behind the post.

SQL Down Under Show Online

Remember back on Halloween when I recorded an episode of SQL Down Under with SQL Server MVP Greg Low? Well, the episode is now online! You can download it in MP3 or WMA format here: http://www.sqldownunder.com/

Enjoy!

Data Source Annotations

This post is something of a follow-up on the “Annotate like you mean it” best practice I mentioned in my best practices post last week. I always figured that the technique was something of a no-brainer, but when I mentioned it to Greg Low in our SQL Down Under conversation last week he seemed a little surprised (in a good sort of way) and if he hadn’t thought of it[1] odds are there are a few people out in SSIS-land who might not have thought of it either.

In my last post I talked about updating an SSIS package to use Common Table Expressions in the source query to replace a complex set of asynchronous transforms in the package’s data flow. The upside of this change was that the package performed better by several orders of magnitude. The downside is that the source query became more complex.

Now this isn’t inherently a bad thing, but it is undesirable in that it makes the package less self-documenting. In addition, because I tend to base my source queries on expressions[2], it is not always a simple task to figure out exactly what query is being executed. Because of this, and because self-description is an admirable goal for any development artifact, I will generally update my data flow with an annotation on the design surface that shows the query next to the data source component. In this scenario, the data flow ended up looking something like this:

As you can see, it is now much easier to understand the data being extracted than it would be to examine the data source component to find out on which variable its query was based, and then examine the variable to understand the expression on which it, in turn, was based.

The obvious drawback of this approach is that as soon as the annotation is created, it is instantly out of date. There is no way to create a “dynamic annotation” in SSIS[3], so there is no enforced relationship between the annotation and the data source component that it describes. This then puts something of a maintenance demand on the package developers, as they must remember to update the annotation each time the source query is updated.

I personally think that this is an acceptable tradeoff. The maintenance cost itself is low, and the benefits of having a well-documented package are considerable. Also, if you follow the best practice of “Get your metadata right first, not later” then you will likely not need to update the source query (or the annotation) very often.

[1] I have a great deal of respect for Greg’s knowledge and experience, so if there’s something that is new to him, it’s worth blogging about.
[2] Which will likely be a post of its own before too long.
[3] Although having annotations based on expressions would be a cool new feature, wouldn’t it?

I Want My CTE!

In my recent Best Practices post I mentioned that it was a best practice to “do it in the data source.” By this, I meant that when using SSIS to extract data from a relational database you can often get much better performance by completing tasks like sorting, filtering, grouping and aggregating in the source query as opposed to completing these tasks in the SSIS data flow. Now don’t get me wrong – the SSIS data flow performs exceptionally well, but it is not always the right tool for every job.

Here’s a specific case in point. I was recently helping someone troubleshoot a poorly performing SSIS package. The developer in question was pulling a variety of date values (different dates and times that described a business entity) from a single table in the source system and loading them into a time dimension in a data mart. As part of the data flow he was taking the seven different source columns and putting them together into a single column like this:

1) He used the Multicast transform to split the one data flow path into seven data flow paths, one for each date column.
2) He created a set of “DateValue” columns with the Derived Column transform, with each one based on one of the date columns from the source table, and replacing NULL dates with a placeholder date value.
3) He used the Sort transform to remove duplicates from each of the derived columns.
4) He used the Union All transform to bring the seven derived columns together into a single “DateValue” column.
5) He used another Sort transform to remove duplicates from the single data column.
6) He loaded the discrete dates into his dimension table.

It looked something like this:



And it performed about as well as you could imagine.

As you can probably imagine, he was a little frustrated, and was saying all sorts of bad things about SSIS which I won’t repeat here and which, for the most part, weren’t true.
And as you can probably also imagine, my solution for this problem was to perform the required logic not in the data flow, but in the source query. In fact, I said “this looks like a job for… Common Table Expressions!” That’s right – since the source system was using a SQL Server 2005 database, we could quickly and easily perform all of this logic in the SELECT statement used by the data flow, and eliminate 90% of the work being done in the data flow. The source query ended up looking something like this:

WITH FilteredDatesCTE
AS
(
SELECT TimeRaised
,TimeOfFirstEvent
,TimeOfLastEvent
,TimeResolved
,LastTimeStateWasModified
,TimeAdded
,LastModified
FROM SourceTable
WHERE LastModified >= '1/1/2007'
),
UnionedDatesCTE
AS
(
SELECT TimeRaised AS DateTimeString FROM FilteredDatesCTE UNION
SELECT TimeOfFirstEvent FROM FilteredDatesCTE UNION
SELECT TimeOfLastEvent FROM FilteredDatesCTE UNION
SELECT TimeResolved FROM FilteredDatesCTE UNION
SELECT LastTimeStateWasModified FROM FilteredDatesCTE UNION
SELECT TimeAdded FROM FilteredDatesCTE UNION
SELECT LastModified FROM FilteredDatesCTE
)
SELECT DISTINCT ISNULL(DateTimeString, '1/1/9999') AS DateTimeString
FROM UnionedDatesCTE;

Of course, if we were using a different database platform for the source system we could have used derived tables instead of CTEs, but the CTE syntax is cleaner and more elegant, and certainly does the trick.

The updated data flow was much simpler – none of the transforms shown above were required anymore – and it performed superbly, completing in minutes where the original data flow with all of its asynchronous transforms took over a day to complete with the same production data volume.

And as you’ve doubtless noticed as well, this example also ties in nicely with the “Don’t use asynchronous transforms” best practice.

So what’s the moral of the story? Yes, this time I actually do have one! The moral is that in order to be an effective SSIS developer, you also need to understand the capabilities of the platforms on which your packages are based. In this case (and in many, many cases) this means understanding T-SQL or some other SQL dialect. If you don’t have a thorough understanding of SQL, you’re going to be at a significant disadvantage when using SSIS. Part of it comes down to choosing the right tool for each job, part of it comes down to understanding the strengths and weaknesses of each tool.

SSIS Best Practices, Part 2

And as promised, here is my personal list of SQL Server Integration Services best practices. This is by no means comprehensive (please see my previous post for links to other best practice resources online) but it's a great starting point to avoiding my mistakes (and the pain that came with them) when you're working with SSIS. Here goes:

• Get your metadata right first, not later: The SSIS data flow is incredibly dependent on the metadata it is given about the data sources it uses, including column names, data types and sizes. If you change the data source after the data flow is built, it can be difficult (kind of like carrying a car up a hill can be difficult) to correct all of the dependent metadata in the data flow. Also, the SSIS data flow designer will often helpfully offer to clean up the problems introduced by changing the metadata. Sadly, this "cleanup" process can involve removing the mappings between data flow components for the columns that are changed. This can cause the package to fail silently - you have no errors and no warnings but after the package has run you also have no data in those fields.


• Use template packages whenever possible, if not more often: Odds are, if you have a big SSIS project, all of your packages have the same basic "plumbing" - tasks that perform auditing or notification or cleanup or something. If you define these things in a template package (or a small set of template packages if you have irreconcilable differences between package types) and then create new packages from those templates you can reuse this common logic easily in all new packages you create.


• Use OLE DB connections unless there is a compelling reason to do otherwise: OLE DB connection managers can be used just about anywhere, and there are some components (such as the Lookup transform and the OLE DB Command transform) that can only use OLE DB connection managers. So unless you want to maintain multiple connection managers for the same database, OLE DB makes a lot of sense. There are also other reasons (such as more flexible deployment options than the SQL Server destination component) but this is enough for me.


• Only Configure package variables: If all of your package configurations target package variables, then you will have a consistent configuration approach that is self-documenting and resistant to change. You can then use expressions based on these variables to use them anywhere within the package.


• If it’s external, configure it: Of all of the aspects of SSIS about which I hear people complain, deployment tops the list. There are plenty of deployment-related tools that ship with SSIS, but there is not a lot that you can do to ease the pain related to deployment unless your packages are truly location independent. The design of SSIS goes a long way to making this possible, since access to external resources (file system, database, etc.) is performed (almost) consistently through connection managers, but that does not mean that the package developer can be lazy. If there is any external resource used by your package, you need to drive the values for the connection information (database connection string, file or folder path, URL, whatever) in a package configuration so they can be updated easily in any environment without requiring modification to the packages.


• One target table per package: This is a tip I picked up from the great book The Microsoft Data Warehouse Toolkit by Joy Mundy of The Kimball Group, and it has served me very well over the years. By following this best practice you can keep your packages simpler and more modular, and much more maintainable.


• Annotate like you mean it: You've heard of "test first development," right? This is good, but I believe in "comment first development." I've learned over the years that if I can't describe something in English, I'm going to struggle doing it in C# or whatever programming language I'm using, so I tend to go very heavy on the comments in my procedural code. I've carried this practice over into SSIS, and like to have one annotation per task, one annotation per data flow component and any additional annotations that make sense for a given design surface. This may seem like overkill, but think of what you would want someone to do if you were going to open up their packages and try to figure out what they were trying to do. So annotate liberally and you won't be "that guy" - the one everyone swears about when he's not around.


• Avoid row-based operations (think “sets!”): The SSIS data flow is a great tool for performing set-based operations on huge volumes of data - that's why SSIS performs so well. But there are some data flow transformations that perform row-by-row operations, and although they have their uses, they can easily cause data flow performance to grind to a halt. These transformations include the OLE DB Command transform, the Fuzzy Lookup transform, the Slowly Changing Dimension transform and the tried-and-true Lookup transform when used in non-cached mode. Although there are valid uses for these transforms, they tend to be very few and far between, so if you find yourself thinking about using them, make sure that you've exhausted the alternatives and that you do performance testing early with real data volumes.


• Avoid asynchronous transforms: In short, any fully-blocking asynchronous data flow transformation (such as Sort and Aggregate) is going to hold the entire set of input rows in memory before it produces any output rows to be consumed by downstream components. This just does not scale for larger (or even "large-ish") data volumes. As with row-based operations, you need to aggressively pursue alternative approaches, and make sure that you're testing early with representative volumes of data. The danger here is that these transforms will work (and possibly even work well) with small number of records, but completely choke and die when you need them to do the heavy lifting.


• Really know your data – really! If there is one lesson I've learned (and learned again and again - see my previous blog post about real world experience and the value of pain in learning ;-) 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.


• Do it in the data source: Relational databases have been around forever (although they did not write the very first song - I think that was Barry Manilow) and have incredibly sophisticated capabilities work efficiently with huge volumes of data. So why would you consider sorting, aggregating, merging or performing other expensive operations in your data flow when you could do it in the data source as part of your select statement? It is almost always significantly faster to perform these operations in the data source, if your data source is a relational database. And if you are pulling data from sources like flat files which do not provide any such capabilities there are still occasions when it is faster to load the data into SQL Server and sort, aggregate and join your data there before pulling it back into SSIS. Please do not think that SSIS data flow doesn't perform well - it has amazing performance when used properly - but also don't think that it is the right tool for every job. Remember - Microsoft, Oracle and the rest of the database vendors have invested millions of man years and billions of dollars[1] in tuning their databases. Why not use that investment when you can?


• Don’t use Data Sources: No, I don't mean data source components. I mean the .ds files that you can add to your SSIS projects in Visual Studio in the "Data Sources" node that is there in every SSIS project you create. Remember that Data Sources are not a feature of SSIS - they are a feature of Visual Studio, and this is a significant difference. Instead, use package configurations to store the connection string for the connection managers in your packages. This will be the best road forward for a smooth deployment story, whereas using Data Sources is a dead-end road. To nowhere.


• Treat your packages like code: Just as relational databases are mature and well-understood, so is the value of using a repeatable process and tools like source code control and issue tracking software to manage the software development lifecycle for software development projects. All of these tools, processes and lessons apply to SSIS development as well! This may sound like an obvious point, but with DTS it was very difficult to "do things right" and many SSIS developers are bringing with them the bad habits that DTS taught and reinforced (yes, often through pain) over the years. But now we're using Visual Studio for SSIS development and have many of the same capabilities to do things right as we do when working with C# or C++ or Visual Basic. Some of the details may be different, but all of the principles apply.

In the days and weeks ahead I hope to have some more in-depth posts that touch on specific best practices, but this will have to do for now. It's late, and I have work to do...

[1] My wife has told me a million times never to exaggerate, but I never listen.

SSIS Best Practices, Part 1

One of the topics on which I've presented quite a bit lately has been that of SQL Server Integration Services best practices. The body of knowledge on how to best use SSIS is small compared to more mature development technologies, but there is a growing number of resources out there related to SSIS best practices.

And here it is. ;-)

In any event, here is a list of links to those online resources I have found related to SSIS best practices:

• SQL Server Integration Services (SSIS) 10 Quick Best Practices from Kuldeep Chauhan (http://blogs.msdn.com/sqllive/archive/2007/05/21/sql-server-integration-services-ssis-10-quick-best-practices.aspx): This is a relatively straightforward list, and presents some "low hanging fruit" for avoiding common mistakes.
• SQL SSIS Best Practices from Roy Ashbrook (http://drowningintechnicaldebt.com/blogs/royashbrook/archive/2007/05/04/sql-ssis-best-practices.aspx): This is more of a collection of links than any truly original material, but it's still good stuff.
• Best Practices from the SQLIS.com Wiki (http://wiki.sqlis.com/default.aspx/SQLISWiki/BestPractices.html): Another short collection of links - hopefully it will grow sooner rather than later.
• SSIS Best Practices - Performance from Ashvini Sharma (http://blogs.msdn.com/ashvinis/archive/2005/09/27/474563.aspx): This one predates the release of SQL Server 2005 so it's a little dated and a little sparse, but it still covers practices that I see people overlooking today.
• SSIS: Suggested Best Practices and naming conventions from Jamie Thompson (http://blogs.conchango.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Suggested-Best-Practices-and-naming-conventions.aspx): Yes, I've saved the best for last. This is the most comprehensive list of SSIS best practices that I have found online to date. Jamie is a great voice for the SSIS community, and has obviously put a lot of thought and work into this post.

I'd also like to point out that there is a decent amount of overlap and a decent amount of contradiction between the various best practices referenced above. Not everyone agrees on what's best, and to a large extent, "best" is determined as much by the context in which you're using SSIS as it is by SSIS itself. Read all of these resources (and everything else, for that matter) with an open mind and apply their guidance with a healthy dose of pragmatism.

And although this doesn't fall into the same "lists of things to do and to not do" category as the links above, anyone who is interested in doing SSIS correctly needs to check out Microsoft's Project REAL (http://www.microsoft.com/sql/solutions/bi/projectreal.mspx) as well.

Of course, I have no intention of stopping here. (The whole "Part 1" thing in the title probably clued you in on this already, right?) At some point - hopefully today, but possibly as late as this weekend - I will be posting my own personal list of best practices, but I wanted to give credit where credit is due before doing so.