I am trying to import an Excel file with 3914 rows of data into an existing table on SQL Server 2016 Express through the SQL Server Management Studio. I imported a similar file last month, but this is a monthly update file, so I deleted everything from the table before I imported the new file. However, now, when I go to finish the wizard and execute everything, I get the following 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 OLE DB Provider for SQL >Server"
Hresult: 0x80004005 Description: "Unspecified error". (SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task 1: There was an error with Destination - IACList.Inputs[Destination >Input].Columns[Approval Number] on Destination - IACList.Inputs[Destination >Input]. The column status returned was: "The value violated the integrity constraints >for the column.". (SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task 1: SSIS Error Code >DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Destination - >IACList.Inputs[Destination Input]" failed because error code 0xC020907D occurred, and the error row disposition on >"Destination - IACList.Inputs[Destination Input]" 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 - IACList" (35) failed with error code 0xC0209029 while processing input "Destination Input" (48). 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)
It looks to me as though the error has something to do with the Approval Number column. When I query the table, it shows 3,824 records on the server after importing, but there should be 3,915 records. I checked the record that I believe would be number 3,824 in the Excel file, and there doesn't seem to be any issue with it. Perhaps I'm misunderstanding the error message, but I don't see a reason the integrity constraint would be violated. It is a primary key, but there is no other field in the column that matches it, so it is unique.