Have you checked the MaximumErrorCount in your SSIS Package ?
Recently we came across a case where the customer was running a Data Flow Task that was executing to it's completion without transferring any data. We finally figured out the whys. Rather than let the good research go to waste, we're posting the results here.
Source: SQLServer OLEDB Provider
Destination: SQLServer OLEDB Provider
Transform: Data Flow Task (DFT)
Frist we thought the Source table was empty. But after querying the table, we found it had 300+ rows. After enabling SSIS log and redeploying the package, we found that the package was encountering a datatype mismatch but weirdly enough, it was reporting successful completion. Then we found that the Maximum Error Count for the package was set to 9999. This explains why the package was silently swallowing error and reporting successful completion.
We changed that to 1 and the package stopped at error as expected.
So, if your SSIS package shows any weird behavior, don't forget to check the MaximumErrorCount field !
Author : Enamul(MSFT), SQL Developer Technical Lead