SSIS failed with error code 0xC0209029

failed with error code 0xC0209029 

I am sure you have just come from a long line of internet searches to find this specific one. KUDOS to you sir, and this post may not have a fix for your error either.

Recently I received the error failed with error code 0xC0209029 error from SSIS when related to inserting rows into an OLE DB destination. The issue occurred during a package that had never failed in production, but had failed in the development environment for several days, but no issue seemed to be able to be found.

After looking around the internet, the variety of things that can cause this are many, but I would like to add one more.

IF you have a situation where you are trying to insert an identity key into a destination table, and the identity key already exists, it will produce this error.  Check to see if the identity key records for your table are not being inserted, and if they are, that the identity records do not already exist.

The situation that caused this error was because someone else was doing development in the dev environment without anyone giving notice, and the keys were for different data entirely, so it took a while to hunt the issue down. This should give you one more idea to look for the issue.