June 12, 2009 at 1:56 pm
I took the SSIS 2005 class last week and am now trying to populate a database. I created a table in a database. There are 4 years worth of data to include. I am importing from a flat file using the SQL server import / export wizard. 2 years worked fine. year 3 is giving me errors. One error referenced the column birthweiht and would not copy anything to the table - stopped after about 5 seconds. I then selected ignore for that column. It copies to the table but I'm getting the following message and need to know how to correct:
- Copying to [Planning].[dbo].[ThomsonEDData] (Error)
Messages
* Information 0x402090de: Data Flow Task: The total number of data rows processed for file "F:\Thomson\AdHocEdRpts\FY 2007\standard.txt" is 3308073.
(SQL Server Import and Export Wizard)
* Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)
* Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_Pat2009Q1'. Cannot insert duplicate key in object 'dbo.ThomsonEDData'.".
(SQL Server Import and Export Wizard)
* Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)
* Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - ThomsonEDData" (112) failed with error code 0xC0202009. 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.
(SQL Server Import and Export Wizard)
* Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0202009.
June 12, 2009 at 3:24 pm
You've got a Primary Key constraint on your table and you are attempting to import a duplicate key. Can you post your table DDL for dbo.ThomsonEDData?
June 12, 2009 at 7:10 pm
I'm having to access the tables via VPN which is slow. There are > 3M rows. But I removed the primary key and did get some of the rows in. Thanks for the help.
June 16, 2009 at 12:35 am
Removing the primary key is definately NOT the answer here. The primary key is there to prevent you from being able to create duplicate rows. Removing it will cause data integrity problems. In my world, hosing the company's data could cost your job.
I would recommend putting the primary key constraint back the way it was and work towards finding out the root cause of wehre the duplicates are comming from and fix that.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply