Some SSIS Package Errors

Question

I have an SSIS package that was created 3 years ago by someone else who has long been gone. I ran it several times over the last year, and now I get these errors, and I don't know why they fit. This ssis package imports from an excel document into a temporary table. As soon as the information is in the temporary table for the second time, it is launched from the temp table and inserts it with additional columns into the production table. These are the errors I get:

[OLE DB Destination [162]] Error: 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: "Unspecified error". [OLE DB Destination [162]] Error: There was an error with input column "UserProfileID" (299) on input "OLE DB Destination Input" (175). The column status returned was: "The value violated the integrity constraints for the column.". [OLE DB Destination [162]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (175)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (175)" 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. [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (162) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (175). 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. 

When I saw the "error with the input column" UserProfileID ", I looked at it in the temp table and it is zero. This is one of the columns that the package should find at startup. But I do not know if this information was found in the second run or if it should be filled for the first time, so I'm not sure if the problem is that I have a problem.

I appreciate any help I can get. If any additional information is needed, I can try to provide it. Thanks to everyone who can help.

Answer 1

Has the table structure changed recently? If the field had no restriction before, and now it has one, then you need to solve the problem in your SSIS package in order to transfer any records that do not match the restriction to the exception table, or you need to find out why the restriction was added when it not suitable for the type of data being imported.

The next place I would look is an Excel file. When a package starts a file for some time and problems suddenly arise, and there were no changes in the structure of the table into which the data is inserted, the data in the file is probably incorrect.

Answer 2

I think the important part of the post:

 "The value violated the integrity constraints for the column." 

Does your destination allow empty values ​​in this field? Can you insert a row manually? If you take out rows with null values ​​in this field, is this successful?

re: your comment the file in which I was sure has no errors If you can identify the rows that will not be imported due to the null values ​​in the field, can you send this set back and ask the guy if the lines are valid, because what do they violate import?

OK, from your additional information, I see that the problem is the null value in the userProfileID field, which does not allow null values. The question then becomes, which process should correctly determine the value of userProfileID? Is this step updateIDs? Is it copied directly from the input Excel file?