Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need better solution to Import from Oracle Expand / Collapse
Author
Message
Posted Saturday, March 30, 2013 1:09 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 12, 2014 10:23 PM
Points: 53, Visits: 911
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.
Post #1437228
Posted Saturday, March 30, 2013 2:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:40 AM
Points: 13,547, Visits: 10,419
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1437243
Posted Sunday, March 31, 2013 9:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 12, 2014 10:23 PM
Points: 53, Visits: 911
I sense long list of code :) 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
Post #1437349
Posted Tuesday, April 2, 2013 1:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:40 AM
Points: 13,547, Visits: 10,419
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1438067
Posted Tuesday, April 2, 2013 11:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 2, 2013 11:12 PM
Points: 1, Visits: 1
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
Post #1438168
Posted Wednesday, April 3, 2013 2:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:40 AM
Points: 13,547, Visits: 10,419
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1438200
Posted Thursday, April 4, 2013 5:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 14, 2013 10:53 AM
Points: 8, 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
Post #1438716
Posted Thursday, April 11, 2013 9:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 9:22 AM
Points: 4, Visits: 58
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.
Post #1441314
Posted Friday, April 12, 2013 1:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 12, 2014 10:23 PM
Points: 53, Visits: 911
@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 :) ) to import the data. But wait and see what experienced DBAs will say..
Post #1441587
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse