A few years ago, Ayende had a post about why he hated SSIS, which brought about some responses (here and here and here) from some other folks. It was a lot of fun had by all (sarcasm). I happened to be doing some work prototyping SSIS and whether it could be used to replace some of the Perl-based implementations we had in place. After only a little bit of effort, we clearly determined that SSIS couldn’t hack it (which is ironic, since getting SSIS to do many common tasks required a lot of hacking, but I digress).
digression: I won’t post the particular comment since it would be even more of a digression than I am wont to do, but what I loved about a response or two was the point that the problem was that Ayende didn’t RTFM. Anyone who knows Ayende knows what a lazy bastard he is, always stopping every time he finds a problem and tossing it off for others to fix (in case anyone doesn’t get it, this is sarcasm). That is, it was true that an issue or two he raised could have been gotten around by reading some documentation, but really….But I digress.
Due to various considerations, I’m working on yet another proof of concept utilizing SSIS. On the surface, it is in its sweet spot: I need to combine data from Sybase, Oracle, SQL Server and flat files, and get all that sweet data into Oracle (source Oracle data is on different servers from destination). This is just what something like SSIS should be able to do, and I imagine that it is the sort of scenario that it hopes to combat something like Informatica. And that would be great, since licensing Informatica is expensive.
One of the key things is that the need to consider SSIS is driven by non-technical requirements, which is to say that some upper-level guy got SSIS approved as a technology, and so there’s a desire to have it implemented to validate that effort.
digression: I actually consider this a valid reason to consider SSIS. When working with larger corporation type people, these sorts of considerations are part and parcel of the gig.
There’s a concept called the ‘pit of success’ where the idea is that when working with a technology or pattern or whatever, the default experience when working with them is to find success easily. Working with SSIS is like working with the ‘Mountain of Success’ where almost nothing works the first time you try it, and requires a Google search to find all of the other people who tried to accomplish the same basic task you were trying to accomplish and also failed, and who couldn’t figure out why.
A lot of it has to do with Oracle (parameterized query anyone? and no, all those fixes/workarounds that apply to and OLE DB Source don’t work with an OLE DB Command). But then there are the fun things like trying to get data from a SQL Server view and having it hang with no reason if you are using a non-data reader source, until you change the source to be a data reader (which I’m sure isn’t reproducible in other environments, but I don’t care, since I care about the environment I’m working in). Or setting up a lookup component to hit an Oracle table and it fails to work as expected, so you change it to use a query, and that works, and then for the hell of it, you change the component back to using the table exactly as it was before and it now works. This latter thing leads to a common SSIS developer practice of changing radio buttons from a failing option to another option and then back, just because it fixed it before (which from Google appears to be fairly common…”Change the option, close the editor, open it, and change it back…it will work now”….well, isn’t that nice).
The dream scenario is a nice one, and if you are myopic, compelling. “You don’t have to write code, you drag and drop these components and set some properties and drag arrows from the output to another component, and it just works. No code. Awesome.” It is awesome. When it works. The problem is that it rarely ‘just works.’ Instead, you find a need to tweak some minor thing and it turns out that the solution is untweakable.
So, I’m well on the road to once again rejecting SSIS as an option, and in a scenario where it should be a shining light.
I guess I should just RTFM (if only the manual showed how to use parameterized queries with Oracle).