id="info"

Data Management

SQL Server 2005 Integration Services makes a good first impression

You never get a second chance to make a first impression. This aphorism applies to so many things — dating and job interviews come to mind. It also applies to software, which I was reminded of when I got to use SQL Server 2005 Integration Services (SSIS) for the first time last week. It was so easy to build my first extract, transfer, and load (ETL) process — it was way easier than anything I've used from Oracle — that I'm starting to regret not making the switch to SQL Server sooner.

I think the part that impresses me most about SSIS is the graphical development tool hosted inside the Visual Studio UI. You basically build your ETL process as a flowchart — lay out pieces that represent each step of your process, connect them like you're drawing a Visio diagram, and then set the properties for each step. I've never had to use this exact kind of tool, but the interface was so easy that I figured out the basics the first day and had my first fully functional ETL process running a day later.

The other part I really like is that the end product is a single file. The entire process that you lay out with the graphical editor is saved into one file, which you can send to somebody else so they can run the process or deploy it to a server where it's run by the SSIS service on a scheduled basis. The one time I had to build something similar for an Oracle environment I ended up with a mess of stored procedures and Java code. It had so many pieces, and it was much harder to manage than SSIS' single file.

I think the one big learning curve is the separation of control flow and data flow. You do things like iterators and branching logic in the control flow panel and then switch to the data flow panel when you have to actually manipulate data within a particular control point. I wasn't always clear when I should use which panel, so I spent a lot of time switching back and forth to find the right widget that I needed for a particular task.

My biggest complaint is that I feel like things were a bit disjointed in the Visual Studio UI. You had to pop open so many dialog boxes or frames to get access to the editors you use to configure each widget. Again, I've only worked on it about a week, so it may get smoother as I get more comfortable. But for right now, I prefer the way Oracle's JDeveloper switches between its multitude of editors; it just feels smoother and more intuitive than how SSIS handles it.

The thing is, Oracle has not chosen to build its competitor to SSIS as an integrated JDeveloper editor. Oracle Data Integrator is a separate Java GUI — it's not integrated into the one good IDE that Oracle has with JDeveloper. I think they missed an opportunity to leverage what is Oracle's best IDE. Oracle could have created a smoother integration that would have compared quite favorably to SSIS in Visual Studio. Instead, I think it's still an advantage to Microsoft.

I'm really impressed so far with SSIS. For the first time in a long while, I am really looking forward to learning more about a database tool. It's such a different feeling than I get from working with Oracle tools.

UPDATE: After posting this blog entry, I learned that Oracle considers the Oracle Warehouse Builder to be its main ETL tool and not Oracle Data Integrator. There is overlap between the two Oracle products, and both Oracle products have overlap with the competing Microsoft SSIS product mentioned in this blog entry. Neither Oracle Warehouse Builder nor Oracle Data Integrator builds upon the JDeveloper platform, though Oracle does intend to migrate in that direction so that its tools integrate into a single UI similar to how Microsoft's tools integrate into Visual Studio.

Additional resources about SSIS on TechRepublic

Editor's Picks