March 30, 2013 at 1:09 pm
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.
March 30, 2013 at 2:32 pm
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 31, 2013 at 9:29 pm
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
April 2, 2013 at 1:08 pm
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).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 2, 2013 at 11:16 pm
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
April 3, 2013 at 2:15 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 4, 2013 at 5:03 am
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
April 11, 2013 at 9:08 am
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.
April 12, 2013 at 1:19 am
@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..
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply