Preserving Primary Key Constraints in DTS Wizard

Hot Topic

System.Data.Sql
Visual Studio Web development possible with SQL2005's Visual Studio?
FREETEXTTABLE
SQLserver2005Express post-install problem
Flat File Connection with quote encapsulated fields
Filter Snapshot Data
ISNUMERIC function
Sql Reporting Services problem
Passing parameter to a dts
The user 'sa' does not have permission to register endpoint 'sql_endpoint' on the specified URL. Please ensure the URL refers t

SQL Server

Running Total cursor
Configuration Error in roleManager in security VSS
Upsize Access 2000
dynamic query
Command line tools missing
Successful Job that doesn't run? HELP
Package could not be loaded - SQL Server Agent
Execute a package on a remote server
[OLE DB] how can i use IRowChange::Insert() to insert data ?
Maximum number of connections

Preserving Primary Key Constraints in DTS Wizard

I have a problem that has been frustrating me for a long time, simply because I know this has to be somewhere but I cant seem to find where to toggle this setting.

I have a large number of tables that I regularly send to a remote server using the DTS Export Wizard in SQL Server Management Studio 2005.

Although I have toggled "Enable Identity Insert", as well as "Drop and Recreate Destination Tables", my tables upon completion are missing their primary key constraints, as well as its Identity attribute.

I have to go in manually to each table and set the column that is the PK, and the Identity attributes - all my stored procs break as a result of the missing constraint.

What setting do I need to flag during the DTS Export that will preserve the key values for me on completion




Answer this question

Preserving Primary Key Constraints in DTS Wizard

  • blackman666

    Try using the Copy Database Wizard instead, the SSIS Wizard does not preserve keys or identity

  • Risky Business

    I'm afraid your best bet may be to script out the database using the Generate Script Wizard and then use DTS to move the data only in that case

  • RJ-X

    Thanks for your reply, Euan.

    Unfortunately I cannot take this approach. In the Copy Database wizard, when I attempt to connect to the remote database that should accept my copy, I get a dialog stating that the remote server does not have the component required to perform the wizard's operation.

    I have limited access to the box; I did not see anything unusual in the way of its configuration; but the help link and documentation did not give me anything specific to look at.



  • Preserving Primary Key Constraints in DTS Wizard
Answer this question