I didn’t realize that there was so much pent up hostility toward Integration Services out there… especially from people who are prejudiced against insects. Heh. Goodness knows that I’ve complained about its warts enough in the past (with a dozen or so unpublished posts), but… Here’s my unsolicited $0.02USD (adjusted for inflation) response to SSIS’ 15 Faults (ayende.com):
- Bad Errors? I’ve griped about the HRESULTs myself, so I (unfortunately) have to concede this one. I would just phrase it differently… Not bad so much as just as unhelpful as IRS reference manuals.
- Random Errors? Having only experienced utterly predictable and frustrating errors with packages (especially debugging custom tasks and components), I’d love to see some repro steps and/or Camtasia recordings of this “random” errors.
- Keeping Track of What It Shouldn’t? Now this is just a silly point to make a nice 15 items. This sounds like somebody doesn’t understand the editor and how configuration is loaded and cached. If you follow the Project REAL best practices for not storing your connection strings and passwords in the package, you probably won’t have these problems.
- Sorry Excuse for Deployment? VS isn’t required for deployment. I wouldn’t even recommend it. I’ve written batch files and MSI add-ins for deploying packages… If you follow the Project REAL best practices for not storing your connection strings and passwords in the package, you probably won’t have these problems. I prefer only the file system for storing packages anyway, so xcopy isn’t all that complicated.
- Security? Who needs that? Why does he think sysadmin privileges are required? What’s he doing that violates the principles of least privilege development? I’d love to see the “packages” in question.
- UI formatting instructions along side with the executable code? I’m boggled to imagine how he’d like to see the package structured instead… With layout information stored in a separate file like XSX files are for XSDs? If the devs did it that way, he’s probably be just as aggravated to have to reconstruct the layout of his tasks and components every time he opened it from a new location that was missing the format file. [I do hate the embedded VSA and Office-style XML blobs… I wish that the DTSX document was a well-designed 100% element-based standard.]
- No thought about version control? Now this another asinine point. Packages are code, just like C# files and anything else. They’re XML and they’re meant to be versioned in your favorite SCC just like anything else. Put ’em in Team System where they belong (or SourceSafe if you’re not up to Team System yet) and they’re versioned, comparable between versions and all that.
- Bad configuration scheme? The configuration scheme is infinitely flexible, and apparently has enough sharp edges for the incareful geek to hurt himself upon. If you follow the Project REAL best practices for not storing your connection strings and passwords in the package, you probably won’t have these problems. The only configuration bits that are horribly broken is the registry options. I have an unposted blog item somewhere complaining about registry support…
- Random configuration scheme? Complaints of “randomness” are just symptoms of more sloppy development practice, in my experience. If a system generates a result once, it’s possible to reproduce it. Now, I will grant you that the interaction between parent and child packages can be difficult to trace if you’re not disciplined about naming conventions and proper engineering discipline…
- Bad UI? I wonder if the author was one of the chorus bitching about how “late” Yukon was? Heh. The editors need the loving caresses of some UXperts, no doubt about it. But until we can shoehorn declarative ETL into the Engine via DDL, we’ll just have to continue making incremental improvements to the UI. I will deliver the Mat Noguchi Memorial Answer here: “If you think you can make it better, please deposit your resume in Careers @ Microsoft.”
- Lack of extensibility? As opposed to no extensibility at all? Writing custom tasks and custom components is a trivial C# task. What’s so hard about that?
- Bad interoperability? We don’t write Oracle’s drivers. They do. In fact, Oracle has fixed quite a few of the problems that they had… Even Teradata is rumored to have a 64-bit driver now. The connectivity story is one that is improving. Several members of the product team have heard the customer feedback loud and clear (from me, too), so expect to hear exciting news on this front soon. Keep an eye on the Connectivity Wiki.
- Busy work? *chuckle* This is why I’ve written a couple of different software factories to generate packages from templates using the target and source schemas as a model… It was never intended for you to manually create a million packages by hand, I don’t think.
- Hard to debug? Don’t dynamically generate your SQL and you won’t have this problem. Packages and customer widgets are hard to debug for other reasons, but if you’re doing sloppy work with dynamic SQL, that’s not SSIS’ fault.
- The missing basics? Date formatting? Date parsing? What I’ve needed is there for expressions to pull the parts of a date out and rearrange them. What’s missing? A good performing UPSERT is not a function of the ETL engine on our platform or anybody elses’ — and you get MERGE with Katmai now.
If you keep at it, you’ll get the hang of it, Ayende. I think you’re making it harder on yourself, though, than you should. Heh.
I see that Phil Brammer beat me to a rebuttal. Go Phil!