I want migrate data from salesforce to SQL server and I am using SSIS connectors for salesforce. I am creating single SSIS package which fetch data for all objects and insert into SQL server. I tried using following connectors for salesforce.
Connector 1 : Kingswaysoft https://www.kingswaysoft.com/
Connector 2 : CData https://www.cdata.com/kb/articles/ado-ssistask-sf.rst
Connector 3 : SSIS PowerPack - https://zappysys.com/onlinehelp/ssis-powerpack/index.htm https://zappysys.com/products/ssis-powerpack/ssis-salesforce-source-connector/
In all the connectors I am unable to provide different columns(salesforce fields) dynamically in SOQL query using SSIS variables.
I agree with the comments that SSIS is for static ETL & you can work around with C# script task for dynamic metadata.
As an Alternative, you can try conditional branches and run two different tasks based on expression. Read Add expressions to precedence constraints.
Not sure how many dynamic columns we are talking here, but for discussion sake let’s take 2 different columns has to be filled in salesforce destination based on source column, then have 2 branch.