The reason you're getting the "No Column Name" error is because you're using temp tables. SSIS can't read metadata from your statement due to this issue.

My workaround is instead of using temp tables, I use permanent staging tables, and divide the failing query into 2 sections:

1. Stage the data

2. Read the data.

Works every time.