I think i found the root of the problem. The package i have is inserting records into the table where the update needs to happen prior to the update. So here's the high level.

Insert records in table A

Update records in table A based on criteria

Now if I want to re-run the package it's supposed to not insert the records that have already been inserted in table A.

The problem i'm seeing is that the SSIS is not catching those records and inserting the records that have previously been inserted in table A causing my update query to update all these records because the update is to catch duplicates.

The fields i'm joining on to see if the record exists are

1) Money field

2) Bit field

3) Varchar field

Now I took the query that checks to see if the record exists and ran it in management studio and it didn't insert it but when the package runs it inserts those records previously inserted in the table.

Does anyone know why this is happening? Why would a query run properly in management studio but when put in an Execute SQL Task it runs differently?