While preparing my demonstration for the code camp listed below, I came across something interesting. The SSIS package read rows from SQL Server 2005 about twice as fast as from Oracle 10g.
Let me start off by describing my environment. I have a Dell Inspiron 9300 laptop with 2Ghz processor, 1 GB ram, 80 GB hd. I have a full install of Oracle 10g Standard edition and a full install of SQL Server 2005 standard edition. I created the same tables in both systems, loaded them with the same count of rows (the same data) totaling about 9.5 million rows.
My data flow tasks simply used the OLE stage to pull data out of SQL Server and Oracle and write directly to a flat file stored on my C:\ drive. (I didn't have them both running at once, I ran the oracle job first, and then the SQL server job next). Neither table was indexed.
After 5 seconds the Oracle job had pulled ~300k rows while SQL server had pulled over 700k rows. There was obvious latency as the output file was written to disk.
Someone mentioned to me that the OLE stage in SSIS has an error communicating with Oracle. I will research this more and find out what effect, if any it has on my test.
After I determine if the OLE stage is affecting the performance, I'll run actual tests with a stopwatch and post the full results on here.
SQL Server 2005 is looking very promising as a key tool for a datawarehouse.