Sounds like there is absolutely no error checking in the SSIS Package. You should have multiple cleansing and staging tables to work thru before sending the data to the Destination. Once the data is pumped into the a "FirstTable" some Verification and Cleansing should take place. Then if some duplicates are found, and pushed to an error table for later viewing by humans, then the good data can be inserted into the other database.

I always cleanse and verify all data. Never use the original table(s) where the data is being stored. If there is an error, like attempting to pass an incorrect data type, that data is moved off to an error table, and a notification is sent out. If there are no duplicates allowed to move, I move the rows that are duplicate into an error table, and again, notification is sent out.

But a SSIS package needs to have some steps to take care of things like this. I also use all stored procedures, just in case I need to modify one and add or remove some code. I can easily do that, without having to deploy the package and configuration all over again.

Andrew SQLDBA