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!
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!