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:
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.
Best Answer
The important portion of that is the following:
So check the data type of the "Approval Number" column in SQL, or any other constraints/foreign keys you've added to the table, and compare that to the values in your new spreadsheet.
If the table was created based on values from the first spreadsheet, it is possible the columns is set to a too-small datatype, or the wrong datatype entirely.
For example, maybe the first spreadsheet contained only numbers in the "Approval Number" column, so the column in SQL has a datatype of INT. But the second one has some rows with letters ("197234b" or "unknown" or something), or rows with values too large for an INT datatype.
You'll need to either change the datatype of that column to something more appropriate for the actual data you are receiving (varchar or BIGINT or whatever), or edit the spreadsheet before you import it to remove any bad values.
EDIT: Also check out the ideas in this Stackoverflow question: looks like Excel sometimes tries to import extra blank rows at the end of the list. You can handle this in one of several ways: