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:-
- for the destination I chose “SQL server native client 10”.
- then I selected copy data from one or more tables or view.
- SSIS run immediately
but i got the following errors,
- 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?
Before Importing date execute the below query to set identity insert on
SET IDENTITY_INSERT TableName ON
// Do the Import Operations
after Importing date execute the below query to set identity insert off
SET IDENTITY_INSERT TableName OFF
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
In the resulting screen, click the
Enable identity insert property and your identities will be replicated over.
Same net result as issuing
SET IDENTITY_INSERT TableName ON & OFF but this is done automagically at build time.