Possibly save 1 hour of your time: In SSIS, I have a data flow task that selects the source with some logic using table variable. It causes this compilation error when you try to save.
SSIS doesn’t know like using table variable in the SQL command.
I have some complex logic so I need to use variable table. Temp table would most likely have same issue. So I just use a stored procedure instead. In my SQL command I have exec MyStoredProcedureName.