Use DTS to move ORacle to SQL

Question

Use DTS to move ORacle to SQL

Ken A

Grasshopper

Points: 15
More actions
November 15, 2004 at 2:44 pm

#165449

I need to move data from an Oracle 9i server to SQL Server. What I want to do is change only get data from Oracle that matches data from a SQL Server query/table. Is there a way to have DTS do this?
In other words, this is not just a straight copy. This is something that will be running every hour and the data that we want to pull down needs to change based on the results of what is in a table or query on the SQL server.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Rather than using straight DTS, have you thought about adding the Oracle server as a 'linked server' and using straight T-SQL to accomplish the data transfer?

Ken A Grasshopper Points: 15 More actions · Answer 2

Yes, but then it essentially pulls all the data over from Oracle which takes a lot of bandwidth over a slow WAN. Also, the only way the linked server will work is by using the OpenQuery() command because of a known bug in the Microsoft drivers and the data types/lengths used on these tables in Oracle (found a QB article on Microsofts website that explained the problem).

I should have been more specific in my original post, but my real purpose for doing this is to narrow the query so it pulls as few records as possible. I have pulled the entire tabvle or sections of it with no problems.

To be more specific, we are a manufacturing facility and we produce certain UPC's in a given day. We want to get some detailed information from the corporate Oracle Servers about these UPC's. The Oracle table is HUGE so I want to minimize the results, since this is going across a slow WAN, to only the UPC's we are producing at a given time.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

Hmmm - tough one. Is there some way that you can start maintaining a "Transfer to SQL Server?" flag against the Oracle data. You will know from the relevant business logic whether this is possible.

If you were to combine that with a "Date Last Modified/Created" field (also to be held on Oracle), you will always know what information has to be sent to SQL Server, without having to perform the initial cross-platform query.

Phil

Ken A Grasshopper Points: 15 More actions · Answer 4

Yea, that would be nice, except this is actually connecting to JDE (Peoplesoft) Enterprise One. Hence, they can make changes to a Work Order and/or UPC so I would have to have a date last modified since I need to know if the status of the UPC or work order has changed. Needless to say, E1 is old (on the back end side even though this is a brand new installation) and those fields don't exist. Also, modifying the underlying tables won't fly with corporate since their motto is not to do any changes to the underlying tables etc. because of future upgrades ..... understandable.

Yea, this is a tough one. I wondered about a way to create the SQL statement dynamically, write it to a text file and use some of the dynamic properties of DTS to read that file for the SQL statement.

If this would work (big if in my mind), I've never done the following so I'm not sure how to do it:

1) Write a SQL Statement from a store procedure to a text file. The stored procedure would generate the actual SQL statement/text and write it to the file.

2) Have DTS read that text file as the query for it to use. I understand that DTS can do this...I think.

3) Delete the text file when it is all said and done for the next download in a hour or so.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

Ken, I'm not sure that I can picture the sequence of events you are considering. Are you saying that you would analyse the existing SQL Server data and then produce a query from that? If so, I guess you're thinking only of having a dynamic WHERE clause? If so, what form will the clause take? - eg:

1) where [pk] between x and y

2) where [date] between x and y

3) where [pk] in (.........................................)

4) ?? something else

Dynamically building a WHERE clause is not a huge issue, as long as you don't have to go down avenue (3) above!

Unfortunately, my DTS experience is only rudimentary - but you might like to check out http://www.sqldts.com for more ideas and info.

Phil

Ken A Grasshopper Points: 15 More actions · Answer 6

You are correct. I would query our SQL Server to come up with a list of the currently running UPC codes, then they would be used in a where clause as per your #3 above. Since they are a 16 digit number and not in any sequence, I can't use #1 or 2 as you list above.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 7

So why not create a table in Oracle that contains UPC codes from SQL Server? This should do away with all that text file business.

Part 1: ensure that the UPC code table is populated and up to date from SQL Server. Your business rules will dictate the most effective way of doing this. Hopefully you don't need to send all of the codes every time.

Part 2: do an inner join between that table and your JDE table and transfer from Oracle only those records that match.

Regards

Phil

Ken A Grasshopper Points: 15 More actions · Answer 8

Yea, we've discussed that (our plant and another with the same issue). Since this is a new implementation corporate wide, I'm not sure if Corporate will be willing to add tables or not. This is an alternative.

Linda Johanning SSCertifiable Points: 6727 More actions · Answer 9

Back to the statement that there is "a known bug in the Microsoft drivers"... Why not use the Oracle 9i driver? We use it in linked servers and DTS and have no issues. Don't know if it will improve bandwidth though.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 10

You may like to keep the new tables in a completely separate database to allay management concerns relating to upgrades of the JDE databases. This would seem to be a completely harmless and non-invasive solution. Good luck.

Phil