Yes, your query is being built at run time, so at design time there's no information to know to show you the columns, which makes it difficult for you to set it up correctly. (You could define the columns manually if you knew what to set.)

Try it with your actual first query, instead of using a variable. When that is set up and working, change it back to use the variable.

In general in SSIS, with anything computed such as a connection name or query string, set it up first with an actual fixed name, then change to use the variable when it's working. That allows SSIS to help you set things up, because it can access the object and know its characteristics.