Hey guys, would would be the best way to copy an entire table from Oracle to SQL Server? An openquery seems inefficient, so I am thinking something with SSIS. I am still new to SSIS and am still learning. At first I thought doing a DataFlow task and just using a SQL query that selects the 6 fields that I need from the table would be good. But then I figured there may be a way to bulk copy the entire table. Basically I want a table on my SQL Server database to be identical to this Oracle table. The oracle table does change on a daily basis. New records are added, and others are modified.
![[H]ard|Forum](/styles/hardforum/xenforo/logo_dark.png){kind=logo}
-
Some users have recently had their accounts hijacked. It seems that the now defunct EVGA forums might have compromised your password there and seems many are using the same PW here. We would suggest you UPDATE YOUR PASSWORD and TURN ON 2FA for your account here to further secure it. None of the compromised accounts had 2FA turned on.
Once you have enabled 2FA, your account will be updated soon to show a badge, letting other members know that you use 2FA to protect your account. This should be beneficial for everyone that uses FSFT.
Copy entire Oracle table to SQL Server
- Thread starter coder_t2
- Start date
Which version of SQL Server do you have? If you use SQL Server 2008 Enterprise edition, check this out:
http://blogs.msdn.com/sqlperf/archi...to-get-data-out-of-oracle-a-big-surprise.aspx
http://blogs.msdn.com/sqlperf/archi...to-get-data-out-of-oracle-a-big-surprise.aspx
Pwyl_The_Destroyer
Limp Gawd
- Joined
- Aug 22, 2005
- Messages
- 139
You could also look in to some of the dynamic ways you can kick off SSIS packages... you should be able to do that via stored procedure and command line.
Actually a weekly update should be alright for this instance. I would just have it sync at night. But I may need to change it to a nightly instance down the line.
Pwyl_The_Destroyer said:
How would I activate SSIS via a stored procedure?
Pwyl_The_Destroyer
Limp Gawd
- Joined
- Aug 22, 2005
- Messages
- 139
I don't have experience with it in 2k5 but I'm sure some quick googling will get you the right proc and how to call it.
If you go that route, you may want to look at a different strategy than a full table copy, depending on how often you'll be calling it and what your response needs are. (ie are you pulling the data into a web page etc.)
I am only putting the data into a table for queries to be run later. I run reports for work, so I would be doing joins or some kind of lookup inside the table. No web pages involved.
So would the best way to do this be to use the DataFlow task and use a query in that to pull all of the data? Once again, I am a newb at a this and have read some tutorials. But still need some guidance as to what is most efficient.
So would the best way to do this be to use the DataFlow task and use a query in that to pull all of the data? Once again, I am a newb at a this and have read some tutorials. But still need some guidance as to what is most efficient.
Ok that looks good. And I think I understand how to create SSIS packages pretty well now. Can I run multiple SSIS packages at the same time? They would be connecting to the same server to pull data from. I know with openqueries, this has caused me problems in the past. Thanks for all the help so far.
Are the different SSIS packages updating rows in the same tables? If so, I'd suggest not running simultaneously -- refactoring the different SSIS packages or run scheduling is advised.
Regarding your openquery comment, I'm wondering if you're using the OracleClient provider in SSIS...
(Open your SSIS project --> Data Sources --> New Data Source --> couple "Next" clicks --> Provider dropdown --> "OracleClient Data Provider")
Just making sure on that setting
Definitely not going to update the same table at the same time. I was just talking about connecting to the same oracle database and copying data over at the same time. I understand that updating the same table can cause a lock out.
I can't seem to find where the Data Sources are...
I can't seem to find where the Data Sources are...
Here is a link to the dataflow picture
http://i764.photobucket.com/albums/xx284/tomiboy59/Work/dataflow.jpg
It's pretty basic, all I am doing is a simple select.
Select distinct col1, col2, col3 from table1. (This is for the oracle table)
And then I have the dataflow import it into a table in SQL Server. And I did try using SQL Server Destination for the dataflow, but apparently there is a bug that was causing me an error. It was, Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". I read up on it, and it is apparently a bug with SQL Server that is fixed with using OLE DB Destination instead.
http://i764.photobucket.com/albums/xx284/tomiboy59/Work/dataflow.jpg
It's pretty basic, all I am doing is a simple select.
Select distinct col1, col2, col3 from table1. (This is for the oracle table)
And then I have the dataflow import it into a table in SQL Server. And I did try using SQL Server Destination for the dataflow, but apparently there is a bug that was causing me an error. It was, Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". I read up on it, and it is apparently a bug with SQL Server that is fixed with using OLE DB Destination instead.
How long does the query execution take when you run it from TOAD? (or some other tool connecting to the Oracle DB)
How many rows are returned in the one query you gave?
What is the total rowcount from that table?
Just trying to eliminate the Oracle database performance as a potential source of the slowdown...
How many rows are returned in the one query you gave?
What is the total rowcount from that table?
Just trying to eliminate the Oracle database performance as a potential source of the slowdown...
How long does the query execution take when you run it from TOAD? (or some other tool connecting to the Oracle DB)
Tough to tell. I use SQLTools which disconnect after an hour. This query takes 4 hours through openquery. I am currently running it, but I think it time out. If you know of a better free Oracle Tool, I'd gladly download it.
How many rows are returned in the one query you gave?
14 million rows
What is the total rowcount from that table?
28 million rows
Tough to tell. I use SQLTools which disconnect after an hour. This query takes 4 hours through openquery. I am currently running it, but I think it time out. If you know of a better free Oracle Tool, I'd gladly download it.
How many rows are returned in the one query you gave?
14 million rows
What is the total rowcount from that table?
28 million rows
I've not yet used SQLTools. Is there a timeout or disconnect timeframe setting you can change? Alternatively, you can check out TOAD. I've used it plenty, and found it very capable.
14 million is a healthy number of rows returned out of 28 million parsed. But something still doesn't sit right about the execution timeframes you're getting, especially given the SQL statement provided. Is there an Oracle DBA and/or network person you can partner with to troubleshoot the query path, indexing, server I/O performance, network speeds, etc.? Another idea would be to see if you can get all 28 million (select column1 from table) within a reasonable time, and then apply the "distinct" aspect at SQL Server (via Reporting Services, a database view, etc.).
Edit: Another idea would be to identify what additional filters you could apply to your existing statement that gives data back, and run regression tests utilizing larger datasets. Then see if your execution timeframes scale linearly or exponentially. For example, filter by all records no older than 3 months. Then expand to 6 months, then a year, then two years, and so on. See if you can identify a pattern.
Last edited:
Ok I am still trying to find time to do what you asked me. Currently I am trying to see how long it takes to run in toad. But I run out of memory from a select, so I did a create table. The time didn't appear in the bottom left though. Is there any other way for me to check how long a query took to execute?
You got a couple options; this thread highlights the ones I was thinking of.
Still no go. The Set timing on didn't work, it gives me an error saying missing or invalid operation. I also went into options and forced to always show time. Only issue is that it doesn't show time for create table. I'll try insert and see how that pans out.
Ok, so even insert doesn't show the time... But I did try running the query with qualifiers and it increases linearly, not exponentially. Next step, I'll try download the whole table and running the distinct locally. Now is the best way to do this to use the Aggregate Task in the DataFlow and just do a group by for every column?
I was thinking a simple dump without any aggregations in the dataflow task, then running a "SELECT DISTINCT..." statement against the SQL 2005 table. But I'm interested in what you find out from your suggestion as well. Try running both approaches and see what you find out.
Ok, I did the simple dump and it took the 3 hrs 30min versus 3 hrs 50min when I ran the distinct on the oracle server. Running the distinct on my local sql server took 4 minutes. I am not even going to bother trying the aggregate function since it takes so long to download the table. I am kind of disappointed. I was hoping SSIS would somehow be more efficient.
To reiterate a previous question: Is there an Oracle DBA at your location that you can go over this with? Or are you the person?
Something still doesn't seem right with the timeframes you're getting, and some maintenance on the Oracle side of the transactions would be the first place to examine and attempt to rule-out.
Something still doesn't seem right with the timeframes you're getting, and some maintenance on the Oracle side of the transactions would be the first place to examine and attempt to rule-out.
My bad for not answering it earlier. There is a DBA, but he chocks it up to it being a lot of data. It's like pulling teeth to get anything useful from him. I usually don't bother unless it's something really important. This isn't super important, since I've figured out a couple work arounds. I might try replicating the table and see how that works out.
Might be a good opportunity to arrange a meeting with you, the Oracle DBA, and some other influential members of your department. Get some more eyes on the goal, pending issues, and concerns. Any decent-sized project is going to require working with other people of varying talents and quirks, so it's better to learn how to manage and leverage sooner.
Last edited: