Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jason2112
Starting Member
17 Posts |
 Posted - 2010-02-05 : 12:57:55
|
| Here's my dilemma: I've been asked to copy a table in an Oracle db to another Oracle db using VS/BIDS. The table has 2 billion rows. Oracle doesn't like some of the SQL commands I've tried, so I'm wondering if one of the SSIS transformation options can help me copy the data in chunks of 1000 rows without deleting the source data. I know there are ways to do it in PL-SQL (LOBs) but I'd like to try it in SSIS for future integration with other SQL databases, etc. Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-05 : 13:02:28
|
| you can do it using a ssis package with data flow task with attunity connectors for oraclehttp://www.microsoft.com/downloads/details.aspx?FamilyId=D9CB21FE-32E9-4D34-A381-6F9231D84F1E&displaylang=en |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-06 : 02:04:49
|
| I don't know much about Oracle, but my approach to very-large-table-transfer is to export the table to a file (preferably using a "native" / binary format) and re-import the file at the target server.SQL Server has a "hint" that enables very fast load if the file is pre-sorted into Clustered Index order, I expect Oracle does too. Something to consider when choosing an ORDER BY for the export!I think any of the "GUI tools" are going to be weak at doing billion-row-transfer, although I have to say that SSIS is quite clever in how it goes about it. But I don't suppose if can minimise (or turn off even) logging for the duration of the import, and logging space could be a bigger headache than data import for that number of rows! |
|
|
|
Jason2112
Starting Member
17 Posts |
Posted - 2010-02-08 : 12:15:39
|
| Thanks for the help. The Oracle Source/Destination plugin for SSIS did the trick. I went from moving 1 million rows @ 45 minutes using the Oracle OLE-DB driver to less than 3 minutes using the plugin! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-08 : 12:23:28
|
| great |
|
|
|
|
|
|
Need help with copying a huge table using SSIS