89

I am using SQL Server 2008 import and export wizard. I need to import a database. I opened the SQL server import/export wizard and I went through the following actions:-

  1. for the destination I chose "SQL server native client 10".

  2. then I selected copy data from one or more tables or view.

  3. SSIS run immediately

but i got the following errors,

Operation stopped...

  • Initializing Data Flow Task (Success)

  • Initializing Connections (Success)

  • Setting SQL Command (Success)

  • Setting Source Connection (Success)

  • Setting Destination Connection (Success)

  • Validating (Error)

Messages Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "ActionID". (SQL Server Import and Export Wizard)

Error 0xc0202045: Data Flow Task 1: Column metadata validation failed. (SQL Server Import and Export Wizard)

Error 0xc004706b: Data Flow Task 1: "component "Destination - AuditActions" (22)" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard)

It seems that I can not import identity columns and timestamps columns, so how I can force these values to be imported?

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
John John
  • 1
  • 72
  • 238
  • 501

3 Answers3

251

With all due respect to Kishore's answer, that approach is valid if you're modifying the packages. Since you're using the import/export wizard, the more straight forward approach would be to check the Enable Identity Insert box on the Column Mappings tab.

In the Import Export Wizard, after selecting the table for copy, click the Edit Mappings... button

enter image description here

In the resulting screen, click the Enable identity insert property and your identities will be replicated over.

enter image description here

Same net result as issuing SET IDENTITY_INSERT TableName ON & OFF but this is done automagically at build time.

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • thanks the identity columns problem is solved, and now i am having problems with timestamps columns. any idea what i can do with them ? – John John Jan 06 '14 at 17:36
  • 1
    Timestamps are mentioned here: http://www.sqlmatters.com/Articles/Common%20Issues%20with%20the%20SQL%20Server%20Import%20and%20Export%20Wizard.aspx – Niall Connaughton Feb 17 '15 at 00:17
  • 19
    If you're importing multiple tables, you can select multiple tables in the list, and then click Edit Mappings to make the Enable identity insert option apply to all selected tables in a single click. – pmartin Jul 13 '16 at 16:32
  • 1
    Kishore solution hasn't solved my problem but the one you offers does. Thanks a lot. – MadJlzz Sep 30 '16 at 07:56
  • I wonder after exporting if it is needed to do:SET IDENTITY_INSERT TableName OFF. Thanks. – Kevin .NET Jun 14 '17 at 17:23
  • I have first modified the table to remove the primary key constraint, then truncate the table. After I have done both of them I then run the export tool. Had I know this earlier, I would save a lot of time. – June Jun 19 '17 at 03:15
  • Try it again on a different table, the export process was stopped by "Violation of PRIMARY KEY constraint 'PK_tblNote'. Cannot insert duplicate key in object 'dbo.tblNote'. The duplicate key value is (1).". (SQL Server Import and Export Wizard) – June Jun 19 '17 at 03:49
  • Thanks for the screen shots! – Ken Palmer Mar 28 '19 at 19:22
31

Before Importing date execute the below query to set identity insert on:

SET IDENTITY_INSERT TableName ON

Then do the import operations.

After importing date execute the below query to set identity insert off:

SET IDENTITY_INSERT TableName OFF
Trevor Reid
  • 3,310
  • 4
  • 27
  • 46
Kishore
  • 846
  • 6
  • 9
1

You can also check the box of Keep Identity in connection manager pane of Destination connection, when creating a data flow string. It worked for me. enter image description here

Praful Thube
  • 11
  • 1