SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need better solution to Import from Oracle


Need better solution to Import from Oracle

Author
Message
Dehqon D.
Dehqon D.
SSC Veteran
SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)

Group: General Forum Members
Points: 235 Visits: 926
I maintain BI that needs to import the data from Oracle every day. I use the import wizard to create package using .Net provider for Oracle. I've over 1400 tables, so I just choose all of them and let the wizard create the package for me, which I'll put it inside the job later on. It takes over 2 hours to create the package. Now the problem Oracle team do the changes in their database all the time, which lead to failure of my package. It happens almost every week or two, then I've to create the package all over again. It's happening almost 2 years. I've tried to ask them notify me their change logs several times, but no luck.

The logic of my import is I do drop all the tables every day, and package creates tables and imports the data.

Is there any solution that will work in this case that I'll do it once and works forever. All I want is latest data with structure from Oracle to SQL Server. I don't know any people from Oracle team, all i have is their database name and one user id to access the Database.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63706 Visits: 13298
You could keep a list of tables you want to import.
You query the metadata tables in Oracle (it should have INFORMATION_SCHEMA.COLUMNS et cetera) to find the table structures.
Create your destination tables dynamically in SQL Server and import the data using OPENROWSET commands.
Try to get some parallellism in there, since you have so much tables.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Dehqon D.
Dehqon D.
SSC Veteran
SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)

Group: General Forum Members
Points: 235 Visits: 926
I sense long list of code Smile I've list of tables that I need. 1400 tables are actually 100 tables with 14 different schema. From every schema I use 40 tables only. But I don't have idea how to get latest tables structures and create tables dynamically, use openrowset and apply parallelism to them. Appreciate if you give a small example for that.

Other opinions are welcome as well
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63706 Visits: 13298
Here's some documentation about the information_schema on Oracle:
http://docs.oracle.com/cd/E17952_01/refman-5.0-en/information-schema.html

Query these tables to find out which columns and datatypes a certain table has.
Construct a CREATE TABLE statement dynamically and execute this on the SQL Server database, but be sure to map the Oracle data types against the correct SQL Server data types.

Then create dynamically a SELECT query inserting the data from Oracle into your SQL Server table (use a linked server and the OPENROWSET command).


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
kanferc
kanferc
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
Hello,
Maybe you can work in a deferent way, do you need all the 1400 tables?
If you need only some of them try to extract only the tables and columns you need.
You also can copy all the DB with SSIS and manage the data in your environment.

Have a nice day

C.Kanfer
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63706 Visits: 13298
kanferc (4/2/2013)
Hello,
Maybe you can work in a deferent way, do you need all the 1400 tables?
If you need only some of them try to extract only the tables and columns you need.
You also can copy all the DB with SSIS and manage the data in your environment.

Have a nice day

C.Kanfer


Did you read the question? The problem is the columns are changing all the time.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
dm-972730
dm-972730
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 45
Hello,

Try converter from http://convertdb.com/oracle/mssql
Check the option "overwrite existing database" in database options at customization step.
Use "bulk insert" option as well to increase the speed.
Make sure "automatic structure update in the session" option is checked at execution stage.
Probably you should create several sessions for each scheme you need to migrate.
You can start several scheduled jobs simultaneously from command line or in GUI mode.

regards,
Dmitry
cstg85
cstg85
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 204
Would it be possible to create an SSIS package that would...

1)Run a sql query to drop all the tables
2)Create the new tables using an Execute T-SQL Statement ??

example:
select * 
into [table_name]
from (oracle_schema.oracle_table)
go



I have setup SSIS packages before that pull data from an Oracle database to a SQL Server database... However, the column names never changed so it was a like to like data transfer. Now... I leave this post in the form of a question because this is something I've never personally done. Hopefully we will hear from someone in the community who has more experience with transferring data from Oracle to SQL Server using SSIS.
Dehqon D.
Dehqon D.
SSC Veteran
SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)

Group: General Forum Members
Points: 235 Visits: 926
@cstg85 Good idea, would be great if it worked... But in reality it won't I think, as your query needs 2 different type of data source in one code. Some more I couldn't find proper tool from toolbox in SSIS to try out the idea.

Otherwise @Koen Verbeeck wouldn't give so complex steps (at least for me Smile ) to import the data. But wait and see what experienced DBAs will say..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search