March 4, 2004 at 1:18 pm
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!
March 5, 2004 at 10:53 am
You have a lot of choices when using DTS to import data. The simplest thing you can do to avoid having to reassign permissions on the destination tables is to just empty the tables before importing the data from Oracle rather than dropping and creating them. Use an Execute SQL Task with either DELETE FROM or TRUNCATE statements for the two tables.
If, for some reason, you are required to drop and create the destination tables, use an Execute SQL Task with GRANT statements for the two tables.
It is possible to put the logic required to update or insert in a DTS package, especially if the rows can be uniquely identified. Use either a Data Transformation Task or Execute SQL Task to see if each row exists.
Greg
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply