SSIS Package – “Subquery returned more than 1 value” – Inserting one record into a table

ssis

SSIS on SQL Server 2008 R2

I have inherited an SSIS package that is failing when importing a spreadsheet. The part of the failure is in a data flow that consists of :-
* an Excel Source Data Flow Component
* a Derived Column Data Flow Component
* an OLE DB Destination Data Flow Component

enter image description here

I have added data viewers on the two flows, and these show only 1 row on each.
enter image description here

The table that I'm importing into is empty.

I'm not sure what else to look at. Any assistance is appreciated. Thank you.

Best Answer

In my case there was a trigger that trying to update one of the rows. The unfortunate thing was that the query in the trigger returned more than one row in the subquery.

As it turned out for me the trigger was only relevant for one particular supplier (an IF condition in the trigger), also luckily for me, the previous occupant of my position hadn't reviewed this trigger, and it was no longer needed.

Removed the trigger. However in other scenarios I would have had to modify the trigger to return one row only.