Hello friends I am moving data from staging db to the destination db in the same instance. This column in the staging is imported from oracle Db. This column has many duplicate values. So when i am using INSERT INTO ..............SELECT that column.........from stagingdb ... It gives error : Msg2627.....cannot insert the duplicate key in theobject..... So tell me is there any way with SSIS or by other method to do this??? Thanksdimrd_SQL

The reason you are getting this error is because there is a unique constraint on the target column. If you need to violate it then one option would be to temporary disable it, is this your intent?Arthur My Blog

Arthurz The target column is PRIMARY KEY. So that is perventing the duplicates. Thanksdimrd_SQL

So do you still need any kind of help?Arthur My Blog

yes as i am working on oracle to sql migration so i need the data in that column no doubt there is duplicate data in that column which is primary key in sql server its an id. So there is no escape i need help to put data in that column. Thanksdimrd_SQL

If it is a one time procedure (or even not) you may want to use the Data Import Export Wizard . Because it is specifically tailored to doing tasks exactly like this it has the option to enable the identity insert. If you will ever want to re-execute the task you can opt for saving this operation as an SSIS package!Arthur My Blog

I did an import and got the error: Copying to [dbo].[AGENCY] (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_AGENCY'. Cannot insert duplicate key in object 'dbo.AGENCY'.". (SQL Server Import and Export Wizard) Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (149)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (149)" 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 - AGENCY" (136) failed with error code 0xC0209029 while processing input "Destination Input" (149). 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) dimrd_SQL

Hello, I have the same exact error, did you resolve this issue? if so, how??? Thanks, James

Removing the primary key from the database may lead to even bigger problems, you may lose data consistency in your database. You rather should investigate, why the values for primary key are duplicated than blindly trying to insert the data. But, to remove primary key you can use: ALTER TABLE dbo.AGENCY DROP CONSTRAINT PK_AGENCY Please remember that doing it will probably lead to invalid data in the database.