January 19, 2004 at 11:38 am
can dts be used to update records in an oracle table?
rob
January 20, 2004 at 12:39 pm
Well, technically, the answer is yes, but this is highly a matter of what you are trying to accomplish. Remember that DTS is primarily an ETL tool. If you are looking to perform batch operations, then you can use the Oracle OR Microsoft OLE DB Provider to communicate with via the Oracle*Net. Know that these providers are not particularly fast. You may want to consider loading a flat file generated by DTS with Oracle SQL*Loader if you have a great deal of data.
If you are dealing with more transactional data, you may want to consider SQL Server to Oracle replication.
January 21, 2004 at 5:36 am
thx for the reply.
the task is a one-off update using a simple script that will look like this:
update OracleTable set OracleField1 = MssqlLField1
from OracleTable, MssqlTable
where OracleTable.Field2 = MssqlField2
part of the problem is that we do not have anyone who knows oracle which is why i hope that dts can be used in this case
January 22, 2004 at 4:58 am
iF YOU WANT TO UDATE THE FIELDS IN THE ORACLE TABLE WITH THE VALUES FROM THE SQL SERVER TABLES THEN YOU CAN TRY DOING IT THROUGH LINKED SERVERS IN SQLSERVER AND RUNNING THE UPDATES IN A DISTRIBUTED TRANSACTION.
I FEEL LINKED SERVERS IS A BETTER OPTION THAN USING DTS JUST RUN UPDATES.
LET ME KNOW IF IT HELPS.
CHEERS;-)
January 22, 2004 at 7:05 am
Speaking of linked servers....I'm trying to link to an Oracle database, using linked server and the 'MS OLE DB Provider for Oracle'. What goes in the 3 boxes, Product Name, Data Source, and Provider String? BOL is zero help. I have links to SQL servers and I'm OK with that, but I can't figure out what info goes in those boxes.
Any help or direction would be appreciated.
Dave
January 22, 2004 at 2:04 pm
Agreed. It will be anything but fast, but for folks who have not used Oracle, at least you can still use TSQL Syntax. The only real difference is that you must include the linked server name qualifier in your from clause. i.e. LinkedServer.DbName.Ownername.Tablename.
January 22, 2004 at 2:10 pm
I only input a value for the data source. This is TNSNames.ora entry that I use with the Oracle Client to connect to the Oracle database. Even if you use the Microsoft provider for Oracle, you are still going to have to install and configure the SQL*Net (or OracleNet) libraries.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply