SQL 2000 DTS Export

Posted on 2005-02-26
Medium Priority
Last Modified: 2013-11-30
I am wanting to export data from a production database to testing database on the same server replacing the data not appending it.  Starting from the Production database I export and the wizard walks me through it.  I choose the source with sql authentication using the user production (owner of the tables) then choose my destination with sql authentication using the user testing (owner of the table).  I did it this way to maintain table ownership.  I then Copy tables and views from source database and choose the tables I want to copy.  Unfortunately this doesn't replace the data only appends it.  I tried the third option which gives me the ability to indicate replace the data but the ownership doesn't get preserved.  

What is the best method to replace data on another database while preserving the original owner.
Question by:ajruiz
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Assisted Solution

sanjusk earned 1000 total points
ID: 13411922
why don't you define the package in which you first put the deletioin task and then the insertion task

Accepted Solution

MichaelSFuller earned 1000 total points
ID: 13413487
1) Add a SQL coonection to DTS package name it 'Source
2) Add another SQL connection name it Destination
3) Add an execute SQL task that truncates the table in the destination-using either the truncate table method, or delete from
4)Add a transform data tyask between the source and desitination
5) Add a workflow from theexecute sql task to the transform data task on success
6) save
7) execute the pacakge
8) done

Expert Comment

ID: 13414049
correct , MichealsFuller is step of what I was asking you.

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question