You keep saying "SSDT" but it sounds like you mean "SSIS", is that right? SSDT and SSIS are not the same thing (SSIS is a tooling set included in SSDT, but SSDT is much more than SSIS).

Anyway, it is possible to do "dynamic" SQL of a sort in SSIS, many (but not all) of the widgets let you execute SQL based on a package variable that can be based on an expression. That expression can reference package parameters, and those parameters can be linked to an "environment" when you deploy the package. And you can have different "environments" at deployment that set the parameter values differently, then when the package executes, it "inherits" the parameter values from the environment it is assigned.

So, you create a package parameter (a string) that defines your database environment. Then, create a package variable that builds your SQL statement (set the "expression" of the variable to a string expression that builds your query), incorporating that parameter value, and then assign the variable to the widget as its query source. For the "Execute SQL Task" for example, that means setting the SQLSourceType to "Variable" and then setting the "SourceVariable" to the package variable you created. When the package runs, the variable gets assigned the expression that creates the SQL Statement to execute.

Wasn't sure if you already know all this and it's not what you are trying to achieve, but thought I'd throw it out there. 🙂