Execute dynamic SQL in Data Flow Task SSIS

Question

Here is what i want to do:

I am selecting SQL scripts from a database and assigning these scripts to a variable in for each loop (imagine that all scripts have different columns). After that, I am trying to execute SQL scripts in Data Flow Task ODBC Source. I am using SQL_COMMAND @variable from Data Flow Task Properties. This option does not work. I guess it is not understanding dynamic columns. SSIS gives me a validation error.

Also, I tried to execute these scripts in Execute SQL Task from a variable. Again my source is ODBC. I assigned full result set to an object variable in Execute SQL Task but it gives me an error. As far as I know, there is an issue about ODBC. So, I can not assign full result set to an object variable in ODBC.

Do you guys have any idea? How can i execute these scripts in SSIS and get the result? I need to get the result and insert it to a destination table.

P.S: I can not use OLE DB connection because of the server issue. All scripts need to be executed in same environment.

Answer 1

SSIS requires that the column metadata is both static and correct between all sources, transformation and destinations. You cannot simply loop through a list of scripts and run the different results through the same data flow.

The only way you could achieve this is to use something like Biml to build your packages for you programatically from your source scripts. There are a couple free and paid ways to develop Biml, such as the free BIDSHelper and BimlExpress (Made by the creators of Biml: Varigence) add ins for Visual Studio or the paid BimlStudio (Also from Varigence).