Bringing In Oracle Tables Weekly and Assigning Permissions 1

[eb24]

Here is the way my DTS package is currently set up:

I have to bring in a couple of Oracle tables from a remote server on a weekly basis. The way the package works is it deletes the current existing tables located on our SQL Server 2K and creates these tables again and brings in the data from Oracle. Unfortunately, I have to do it this since not only are NEW data added to those Oracle tables, but many EXISTING tuples are also UPDATED, thus it's best to copy EVERYTHING in.

As you can tell, I don't know if this is the BEST way to accomplish this. Not only is there a lot of data, but once those tables are brought over, I have to reassign PERMISSIONS to those users that had access on them before. I don't want this to be a manual job every week.

I would like to ask for your advice as to some suggestions to my dilemma. Is there a way to assign permissions to these tables via DTS? Or do you suggest I redesign my DTS package so it only updates my existing tables (this would be ideal, but don't know how to exactly do this since rows can be updated and inserted)?

Any and all advice will be greatly appreciated!

 

[heidi88]

First, as long as you don't DROP the existing table, you don't need to re-assign the permissions. You can use either DELETE or TRUNCATE statement.

Secondly, if you don't want to do complete refresh, first task you have to do is to compare Oracle table with existing SQL table to mark new and modified records in Oracle table or a temp table, then perform insert if 'new' or update if 'modified' in the existing SQL table. I think complete refresh might be the easies way for you.