1

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.

Improve this question

1 Answer 1

Reset to default
0

The important portion of that is the following:

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."

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:

  • Press CTRL-END in Excel, this will take you to the bottom of where Excel thinks there is data. Delete (don't just clear) those entire extra rows.
  • You can change the constraints on the destination to "Allow NULLs" in the destination rows, then delete them from SQL afterwards
  • You can change the filter criteria for your input in SSIS to exclude any rows with missing values
  • You can add an "error" output to the data transformation in SSIS to output any failing rows to a different dataset, which you can either ignore, or insert into a table with no constraints (all varchar(255)s, ALLOW NULL on every column, etc). That way if there are different errors in the future, you can see right away which rows were an issue.
Improve this answer
5
  • In both spreadsheets, the approval number is a uniform format XX1234567 (2 letters, then 7 numbers). The datatype of the Approval number column in SQL is varchar(9). I have run multiple formulas in excel to make sure that all approval numbers are 9 characters and that none of them are duplicated.
    – Josh Eblin
    Commented Apr 27, 2017 at 15:25
  • @JoshEblin Hmm. Check out the ideas in this Stackoverflow link, then. It might be trying to import some extra blank rows, which you can either allow (by changing the ALLOW NULLS option), or pipe the failed rows into a different dataset in SSIS, to handle the failing rows separately.
    – BradC
    Commented Apr 27, 2017 at 15:30
  • It's not a foreign key, is it?
    – Erik Reasonable Rates Darling
    Commented Apr 27, 2017 at 15:31
  • @BradC every column in that table is required. In the wizard "Edit Mappings..." screen, I have made sure that there are no "Nullable" boxes checked. I even went so far as to try to check one and uncheck it, only to find that it wouldn't let me check the nullable box for any of the columns. I imagine that's because they are all required fields. All that said, I tried the "delete rows below the data in the spreadsheet" suggestion from the link you posted, and that worked. My only guess is that because those rows previously had data, it was causing issues on the import. Thanks for the help.
    – Josh Eblin
    Commented Apr 27, 2017 at 16:06
  • @JoshEblin Yeah, that's an Excel quirk, it doesn't always adjust the "active region" when you delete data at the end of a list. Glad it helped, I'll edit that part into my answer, since that is what worked for you.
    – BradC
    Commented Apr 27, 2017 at 16:19

Your Answer

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

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