After struggling for quite a while and trying every suggestion in several blogs and forums I found that is was quite simple to solve a failed execution message. The interesting part is that everywhere you could see hints about identity. As you will see this is just part of the equation.
I had a small DNN project where I needed to import some source file into SQL. Since Both SQL and IIS are in the same box, identity shouldn’t matter that much as long as the account running the website (Pool Identity in IIS 7) has enough permissions. And THAT is the other side of the equation. After assigning NetworkService to the SQL BulkAdmin role, the package ran fine. I am still trying to find out if there’s a smaller set of perms that can be applied, since BulkAdmin is a server wide role.