I'm trying to create a package to transfer data from a SQL 2000 database to
a SQL 2005 database.

The database already exists and containes data.

I want to REPLACE the data but when I dod this I get an error because the
package seems to be using TRUNCATE TABLE but all the tables have foreign
keys on them.

Should I drop all tables first ?

How do folks sugest I do a daily transfer of data from one database to
another using dts?

--
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com

Hello,

You can select the "Drop any database on the destination server with the
same name, then continue with the database transfer, overwriting existing
database files." option if you use SQL server 2005 Copy Database wizard.
The following steps are for your reference:

1. Start SQL server 2005 management studio.
2. Right-click the database you want to copy, select "Tasks > Copy Database"
3. Follow the wizard to copy database, make sure you select the "Drop any
database on the destination server with the same name, then continue with
the database transfer, overwriting existing database files." option.

I hope the information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

I know I can Drop the database first but I'm looking to do this from a DTS
(SSIS) package

And Copy Database will NOT work because my Source is SQL 2000 and the
destination is SQL 2005 I only have dbo rights.

--
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com

"Sophie Guo [MSFT]" <v-sguo (AT) online (DOT) microsoft.com> wrote

Hello,

Bases on my test, we can use the SQL Server Import and Export Wizard to do
the job.

After starting the SQL Server Import and Export Wizard, on the "Select
Source Tables and Views" screen, we can click the Edit button. And then
select "Drop and re-create destination table" option in the "Column
Mappings" window.

I have tested the issue on my side successfully. For more information, you
may want to refer to the following web site:

How to: Start the SQL Server Import and Export Wizard
http://msdn2.microsoft.com/en-us/library/ms140052.aspx

Creating Packages Using the SQL Server Import and Export Wizard
http://msdn2.microsoft.com/en-us/library/ms141209.aspx

If the issue still exists, please post here the exact error messages you
received for research. Please post here the exact steps to reproduce your
issue. I will test it on my side.

As for copy database wizard, you can use copy database wizard to copy a SQL
server 2000 database to a SQL server 2005 server. For the required
permission for running the copy database wizard, you can refer to the
information in the "Using the Copy Database Wizard" in SQL server 2005
books online:

Required permissions For the Copy Database Wizard to work, you must have
the correct permissions, depending on how you are copying the database, as
follows:

" For the Detach and Attach Method, you must be a member of the sysadmin
fixed server role on the both source and destination servers.
" For the SMO Transfer method, you need to be a database owner for the
source database, and on the destination server you must either have been
granted the CREATE DATABASE permission or be a member of the dbcreator
fixed server role.

For more information, access to the following web site:

Using the Copy Database Wizard
http://msdn2.microsoft.com/en-us/library/ms188664.aspx

I hope the information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Please tell me how I use the Import Export Wizard in a DTS (SSIS) package ?

What you are describing to me is a manual process - I need to automate this
every night.

--
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com

"Sophie Guo [MSFT]" <v-sguo (AT) online (DOT) microsoft.com> wrote

Hello,

Please disregard my last post - this works thanks.

--
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com

"Sophie Guo [MSFT]" <v-sguo (AT) online (DOT) microsoft.com> wrote