Dynamic SQL Script path in SSIS Data Flow task

Question

I need to store all the SQL Queries under one folder and refer in the SSIS package to better organize the list of SQL file I am using so we can easily change the SQL file later without having to rebuild the package. This will include all queries that I am using for "Execute SQL task " as well as the queries in "OLE DB Data Source" under Data Flow component.

Under Data Flow task, Instead of putting the SQL query for source data base into the Query Window: I see four options under Data Access mode for OLE DB Data source- 1. Table or View 2. Table of view name variable 3. SQL Command 4. SQL Command from variable

I understand I could use a variable, store the query in the variable and refer it in "Execute SQL Task" but I am looking for a way to store all the queries in SQL files and it in Data Flow component as well as in "Execute SQL Script Task". I can't seem to find a way to make it dynamic in Data Flow task. Can anyone help with this please?

Answer 1

I don't think storing them as sql files is any good for any type of scenario. Here is what I would do given what you have described.

You can store the queries as varchar in a table in a database instead of as files. Then you can foreach-loop over the result set and map each row to the variable that you would then use as the query for your oledb data source in the dataflow.

So create a variable and make a for each loop that loops over "select query from dbo.queries" or what ever. Set the output to the variable you created and in the container create your dataflow and set either the source-task with an expression or with "SQL Command from variable".

As for the control flow queries why not just have them be stored procedures that you can change when you need to?