SSIS package failed collation issue.

You'll get this problem when your user databases have a different collation from the master database. When you make a temporary table it uses the collation from master/tempdb (which should always be the same). Then when you inner join to a table in your user database you'll get a collation error.
To fix this.
short term #1 always put collation statements on your temporary tables to match tables in your user database.
long term #2 ensure that your master/tempdb collations always match your user databases.