February 28, 2011 at 1:15 pm
When my data flow gets to the OLE DB Destination it hits a performance roadblock. The Insert that SSIS generates for the step contains "returning rowid into :#var" which I would like to be rid of.
Ex. INSERT INTO TARGET_TABLE( "TABLE_NO", "ENTRY_NO", "TABLE_
DATA", "DTTM") VALUES (:1,:2,:3,:4) RETURNING ROWID INTO :5
Variables 1-4 are defined on the mapping page. I don't even know where variable 5 is coming from.
How can I make SSIS stop generating this superfluous code?
April 27, 2011 at 2:32 pm
Performance is probably not poor because of returning statement. Check for triggers on destination table. Or it has (too)many indexes.
May 4, 2011 at 6:10 am
The OLE DB destinations for non-SQL databases are pretty slow generally and isn't due to the returning of rowid. It's inserting and committing every row one by one which is *slow*.
If you use Enterprise Edition, you could try using the Attunity driver set for Oracle which is said to be much faster (and has a fast load option similar to a bulk insert I believe).
SSIS is optimised for pulling data out of external sources into SQL Server rather than the reverse, so it's never going to be as fast as a SQL Server/Flat File Destination.
Another option would be to output to a flat file, then call SQL*Loader to load the data from Oracle's end.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply