2

I am using Import and export data option to import data from one database to another database. Both database have similar structure of tables. All the tables have primary key which is identity element(auto incremented by 1) .Both source and destination tables have rows.

Now when I import data I get error like Failure inserting into the read-only column

So I enabled enable identity insert check-box and tried to import data. In this case I got error as shown below

Copying to [dbo].[Misc_Data] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PK_Misc_Data'. Cannot insert duplicate key in object 'dbo.Misc_Data'. The duplicate key value is (1).".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (50)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (50)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - Misc_Data" (37) failed with error code 0xC0209029 while processing input "Destination Input" (50). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

I think when i enable identity insert on SQL try to insert rows with values same as in souce table for identity column and gives error.

So how can I import data in this case. When having data in both source and destination and identity column is set as primary key.

EDIT The query I used to import table except one column(identity column) is shown below

SELECT [Name]
  ,[Status]
  ,[Last_Checked]
  ,[RecievedDate]
  ,[RecptNo]
  ,[Date2]
  ,[processedDate]
  ,[ind_num]
  ,[AcNo]
  ,[returnType]
  ,[sign]
  ,[oldStatus]
  ,[Acno_old]
  ,[Remarks]
  ,[FileName]
  ,[Status_checked]
  ,[Date_Time]
  ,[By]

FROM [dbname].[dbo].[mytable]

In this case i get error like

- Pre-execute (Error)
Messages
Error 0xc0202004: Data Flow Task 1: The number of columns is incorrect.

(SQL Server Import and Export Wizard)

Error 0xc0202025: Data Flow Task 1: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
 (SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task 1: component "Destination - Query" (73) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)

Both tables have same structure and same datatypes. Still i am getting error.

9
  • We need more information here. Is this a one-off import? Does the source have all the data you need or do you need to keep some of the existing destination data? Are there records using the Primary Key of the source table as a Foreign Key? Sep 8, 2014 at 13:36
  • 1
    "Violation of PRIMARY KEY constraint 'PK_Misc_Data' This seems to be your problem. You're inserting dupes. The duplicate key value is (1). you seems to already have an identity value of 1 in your destination table.
    – Zane
    Sep 8, 2014 at 14:06
  • @MarkSinkinson I want to import all data(rows) from source. There is no forgien key. So id column can be incremented. Sep 8, 2014 at 14:08
  • 1
    Why don't you just exclude that column if it's an Identity value that isn't referenced elsewhere?
    – Zane
    Sep 8, 2014 at 14:09
  • @Zane Ok. I will check. But how to exclude one column during import? Sep 8, 2014 at 14:09

1 Answer 1

13

Don't just use the whole table. Instead choose the option to write a query.

BasicInstructions

Then simply write a select statement that doesn't use that Identity Column. SoBasic

Alternatively, when you map columns, if you click "Delete rows in destination table", it will truncate the target table before loading data. If the opportunity exists for data to be removed from the source system and it should have remained in the destination, then this step will not be right for you.

enter image description here

Your package would look something like this

enter image description here

10
  • It worked for a table which didnt had rows in destination and didnt work for others . I get error like The number of columns is incorrect. (SQL Server Import and Export Wizard) Error 0xc0202025: Data Flow Task 1: Cannot create an OLE DB accessor. Verify that the column metadata is valid. (SQL Server Import and Export Wizard) Error 0xc004701a: Data Flow Task 1: component "Destination - Query" (73) failed the pre-execute phase and returned error code 0xC0202025. (SQL Server Import and Export Wizard) Sep 8, 2014 at 14:47
  • Make sure your column mappings are set right and that your source and destination have the same data types.
    – Zane
    Sep 8, 2014 at 14:48
  • Yes. columns datatype are same. Anything to give other than select query? Sep 8, 2014 at 14:50
  • Can you post the query?
    – Zane
    Sep 8, 2014 at 14:51
  • 2
    freehand "truncate" is the bomb-diggity, yo!
    – swasheck
    Sep 8, 2014 at 22:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.