If you need to dynamically create the SQL statement - here is one possibility:
- Create a stored procedure on the source/destination system that generates the SQL as needed. This should not utilize dynamic SQL to query - rather it should use parameters to read from configuration tables to determine how to build the final query.
- Use an Execute SQL Task to execute the stored procedure with the appropriate parameters. Use output parameters to return the constructed SQL statement (and other configuration items if needed) - or you can use a resultset if you need to execute multiple queries that are all constructed with the same column values to be returned. Note: you could even return many different SQL statements and branch to different data flows based on each individual query.
- The output parameter - or one of the columns returned in a resultset will be mapped to a user variable. That variable will then be utilized as the source in an OLEDB Source.
The important part of this is that your stored procedure does not need to utilize dynamic SQL. It uses input parameters to read data from a configuration table and builds the SQL based on the rules you define - and you don't use any type of string manipulation based on the parameters, only based on the values you get from your configuration table.