?
Solved

How can I import Oracle 10g data into a sql2008r2 db daily.  Linked server crashes. SSMA is unstable and tedious

Posted on 2015-02-12
6
Medium Priority
?
106 Views
Last Modified: 2015-02-23
I have to import a 20gig 10g database into a sql server database on a weekly basis.  My linked server & sql script.  The SSMigration Assistant is tedious and usually fails.  I've been old SSIS is very complex
0
Comment
Question by:donsql
  • 3
  • 2
6 Comments
 
LVL 11

Accepted Solution

by:
HuaMinChen earned 2000 total points
ID: 40607090
Hi,
You can use Stored proc to transfer data through linked server and call the scheduled job to call Stored proc periodically.
0
 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 40607455
You can use the SQL Server Replication and configure the Oracle database as the Publisher and the SQL Server database as the Subscriber and then schedule the Snapshot job to run in a weekly basis.
0
 

Assisted Solution

by:donsql
donsql earned 0 total points
ID: 40608117
Very true.  but I need to import the entire 770 + table 20 gigs of data.  Replication isn't feasible
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 40608130
Why not? You can set the Snapshot to run on a non-working period.
0
 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 40618340
You can use the Database Wizard feature in SSMS. Personally I don't like it but maybe fits in what you are looking for.
0
 

Author Closing Comment

by:donsql
ID: 40625353
I've used replication extensively.  It's not a viable solution for this many tables.  Stored procs aren't stable enough.
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question