Urgent:: Loading Oracle Order data into Sql Server DB daily
Hi,
Our main database is in sql server 2000.
We have to interface with another database (Oracle v ??) to pull orders
I have the sql query to pull orders from Oracle.
I could use ASP / COM to load them into a ADO recordset.
I want to load them into a temp table on SQL Server and then create a stored procedure or some ASP code to re-insert the data into a different Order tables in sql server.
Scheduled Task at midnight: I need to do this on a nightly basis. So I need to schedule a JOB on the w2k server to do this.
I would like to design this so that I have a minimin of changes should the source oracle database or target sql server database change also.
1. Anyone know of a quick way to populate a temp table in SQL Server from an
external Database. ?
2. Advice on whether to use SQL stored procedures or ASP script / COM to access the
data from Oracle and then insert the data into SQL server.
3. Use of SQL Server Utility DTS, I could specify a source as the Oracle database with a sql query to extract the data and copy to the sql server database (work table ). But I want to be able to programmatically trigger this DTS task and trigger a stored procedure to process the loaded sql server data into the real Order entry tables.
Thanks, Paul
W2K Server SQL SERVER 2000, IIS 5.0 ASP / VB / COM+
Our main database is in sql server 2000.
We have to interface with another database (Oracle v ??) to pull orders
I have the sql query to pull orders from Oracle.
I could use ASP / COM to load them into a ADO recordset.
I want to load them into a temp table on SQL Server and then create a stored procedure or some ASP code to re-insert the data into a different Order tables in sql server.
Scheduled Task at midnight: I need to do this on a nightly basis. So I need to schedule a JOB on the w2k server to do this.
I would like to design this so that I have a minimin of changes should the source oracle database or target sql server database change also.
1. Anyone know of a quick way to populate a temp table in SQL Server from an
external Database. ?
2. Advice on whether to use SQL stored procedures or ASP script / COM to access the
data from Oracle and then insert the data into SQL server.
3. Use of SQL Server Utility DTS, I could specify a source as the Oracle database with a sql query to extract the data and copy to the sql server database (work table ). But I want to be able to programmatically trigger this DTS task and trigger a stored procedure to process the loaded sql server data into the real Order entry tables.
Thanks, Paul
W2K Server SQL SERVER 2000, IIS 5.0 ASP / VB / COM+
But I want to be able to programmatically
trigger this DTS task and trigger a stored procedure to process the loaded sql server data into the
real Order entry tables.
This is not a problem. Whatever you do should be executed by a scheduled job (either dts or stored procs). This job can then either be started by a schedule or from a stored procedure call using sp_start_job.
You have several processes here.
extract from Oracle
import to sql server (to staging table)
insert to production table
Certainly the production table insert should be kept separate i.e. this should not be dependant on the Oracle structure but only on the staging table. It should also be done via a stored proc.
It depends how much control you have over the Oracle server as to how you do this. I prfer to export to a flat file (csv?) and then import to the staging table via bulk import. In this way you keep the Oracle server in control of the file creation without any interaction between the servers.
You could as you say probably use dts or a linked server to access it.
As long as you keep this stage separate then you can change the method at will without too much impact.
trigger this DTS task and trigger a stored procedure to process the loaded sql server data into the
real Order entry tables.
This is not a problem. Whatever you do should be executed by a scheduled job (either dts or stored procs). This job can then either be started by a schedule or from a stored procedure call using sp_start_job.
You have several processes here.
extract from Oracle
import to sql server (to staging table)
insert to production table
Certainly the production table insert should be kept separate i.e. this should not be dependant on the Oracle structure but only on the staging table. It should also be done via a stored proc.
It depends how much control you have over the Oracle server as to how you do this. I prfer to export to a flat file (csv?) and then import to the staging table via bulk import. In this way you keep the Oracle server in control of the file creation without any interaction between the servers.
You could as you say probably use dts or a linked server to access it.
As long as you keep this stage separate then you can change the method at will without too much impact.
ASKER
Hi,
You have several processes here.
1. extract from Oracle
using a sql query with a multi-table join
I have to "Pull" the data from the oracle server myself.
If I use DTS to connect to the oracle and dump to a flat file then I "lose" the advantage of the meta data and column names to load into the staging table.
2. import to sql server (to staging table)
basically just a copy of the oracle data
use DTS to read from oracle and insert into the staging table ( I want to be able to delete the staging table first)
Suggestion here was to use a table trigger to start loading into production table but it is not that easy to populate all the target order tables.
3. insert to production table
multiple tables ( order , order details, transactions, receipts )
good points !
1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this
2.
You have several processes here.
1. extract from Oracle
using a sql query with a multi-table join
I have to "Pull" the data from the oracle server myself.
If I use DTS to connect to the oracle and dump to a flat file then I "lose" the advantage of the meta data and column names to load into the staging table.
2. import to sql server (to staging table)
basically just a copy of the oracle data
use DTS to read from oracle and insert into the staging table ( I want to be able to delete the staging table first)
Suggestion here was to use a table trigger to start loading into production table but it is not that easy to populate all the target order tables.
3. insert to production table
multiple tables ( order , order details, transactions, receipts )
good points !
1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this
2.
ASKER
Hi,
You have several processes here.
1. extract from Oracle
using a sql query with a multi-table join
I have to "Pull" the data from the oracle server myself.
If I use DTS to connect to the oracle and dump to a flat file then I "lose" the advantage of the meta data and column names to load into the staging table.
2. import to sql server (to staging table)
basically just a copy of the oracle data
use DTS to read from oracle and insert into the staging table ( I want to be able to delete the staging table first)
Suggestion here was to use a table trigger to start loading into production table but it is not that easy to populate all the target order tables.
3. insert to production table
multiple tables ( order , order details, transactions, receipts )
good points !
1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this
2.
You have several processes here.
1. extract from Oracle
using a sql query with a multi-table join
I have to "Pull" the data from the oracle server myself.
If I use DTS to connect to the oracle and dump to a flat file then I "lose" the advantage of the meta data and column names to load into the staging table.
2. import to sql server (to staging table)
basically just a copy of the oracle data
use DTS to read from oracle and insert into the staging table ( I want to be able to delete the staging table first)
Suggestion here was to use a table trigger to start loading into production table but it is not that easy to populate all the target order tables.
3. insert to production table
multiple tables ( order , order details, transactions, receipts )
good points !
1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this
2.
ASKER
Hi,
You have several processes here.
1. extract from Oracle
using a sql query with a multi-table join
I have to "Pull" the data from the oracle server myself.
If I use DTS to connect to the oracle and dump to a flat file then I "lose" the advantage of the meta data and column names to load into the staging table.
2. import to sql server (to staging table)
basically just a copy of the oracle data
use DTS to read from oracle and insert into the staging table ( I want to be able to delete the staging table first)
Suggestion here was to use a table trigger to start loading into production table but it is not that easy to populate all the target order tables.
3. insert to production table
multiple tables ( order , order details, transactions, receipts )
good points !
1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this
2.
You have several processes here.
1. extract from Oracle
using a sql query with a multi-table join
I have to "Pull" the data from the oracle server myself.
If I use DTS to connect to the oracle and dump to a flat file then I "lose" the advantage of the meta data and column names to load into the staging table.
2. import to sql server (to staging table)
basically just a copy of the oracle data
use DTS to read from oracle and insert into the staging table ( I want to be able to delete the staging table first)
Suggestion here was to use a table trigger to start loading into production table but it is not that easy to populate all the target order tables.
3. insert to production table
multiple tables ( order , order details, transactions, receipts )
good points !
1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this
2.
ASKER
oops, I somewho submitted without finishing...
3. insert to production table
multiple tables ( order , order details, transactions, receipts )
The input staging table will have multiple detail lines for each line in the order. I will need to populate a order record and then order details and also a transaction and receipt table also with the total order amounts. For instance, I have to insert a new record into the order table and use the identify order number to build the order detail rows , trans and receipt tables too.
1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this
Thanks, Paul
3. insert to production table
multiple tables ( order , order details, transactions, receipts )
The input staging table will have multiple detail lines for each line in the order. I will need to populate a order record and then order details and also a transaction and receipt table also with the total order amounts. For instance, I have to insert a new record into the order table and use the identify order number to build the order detail rows , trans and receipt tables too.
1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this
Thanks, Paul
ASKER
oops, I somewho submitted without finishing...
3. insert to production table
multiple tables ( order , order details, transactions, receipts )
The input staging table will have multiple detail lines for each line in the order. I will need to populate a order record and then order details and also a transaction and receipt table also with the total order amounts. For instance, I have to insert a new record into the order table and use the identify order number to build the order detail rows , trans and receipt tables too.
1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this
Thanks, Paul
3. insert to production table
multiple tables ( order , order details, transactions, receipts )
The input staging table will have multiple detail lines for each line in the order. I will need to populate a order record and then order details and also a transaction and receipt table also with the total order amounts. For instance, I have to insert a new record into the order table and use the identify order number to build the order detail rows , trans and receipt tables too.
1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this
Thanks, Paul
If you want to schedule the DTS package or an SP I would use the sql server scheduler - it is easy to use and is probably what you are using for backups anyway.
ASKER
hi,
I don't have any control over the oracle server regarding scheduling jobs to produce extracts for me daily.
I have to "pull" the data myself.
I think SQL SVR DTS is appropriate then.
Paul
I don't have any control over the oracle server regarding scheduling jobs to produce extracts for me daily.
I have to "pull" the data myself.
I think SQL SVR DTS is appropriate then.
Paul
You could use DTS or a linked server with openquery or an openrowset command probably.
ASKER
Hi
Not familiar with this:
a linked server with openquery or an openrowset command probably.
We can create a Linked Server to an Oracle Instance ?
can you give a link or example ?
Thanks, Paul
Not familiar with this:
a linked server with openquery or an openrowset command probably.
We can create a Linked Server to an Oracle Instance ?
can you give a link or example ?
Thanks, Paul
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi nigelrivett,
This looks neat!
But I don't need both of these right ?
What is the difference ?
sp_addlinkedserver
'OrclDB',
'Oracle',
'MSDAORA',
'OracleDB'
GO
EXEC sp_addlinkedserver
'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
Back late tonite.
cheers, Paul
This looks neat!
But I don't need both of these right ?
What is the difference ?
sp_addlinkedserver
'OrclDB',
'Oracle',
'MSDAORA',
'OracleDB'
GO
EXEC sp_addlinkedserver
'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
Back late tonite.
cheers, Paul
Nope, you add the linked server once which defines the link to the Oracle database then can use the link at any time.
ASKER
nigelrivett,
Thanks for your help. I wasn't aware of a linked server to Oracle ( are there capabilities to all ODBC database ???).
I will try these options when we get the oracle client configured on the SQL 2000 server.
Regards, Paul Chu
Thanks for your help. I wasn't aware of a linked server to Oracle ( are there capabilities to all ODBC database ???).
I will try these options when we get the oracle client configured on the SQL 2000 server.
Regards, Paul Chu
Then create an Insert trigger on that table to take the data and put it into the real tables. This will also allow you to validate it all before you put it into your main tables.
Hope that helps