Can I use .sql query file as a data source in SSIS

Question

I have a fairly sizeable query saved as a .sql file (in SQL Server 2019). I would like to use this file as a data source in SSIS. Is this possible? If so, how can I do this?

Answer 1

Hi AliAhmad-4517，

In OLEDB editor, you may browse .sql file as command to use it as data source.

With Execute SQL Task in SSIS, you may select a text file or .sql file that contains an SQL Statement using a File Connection manager.

https://www.sqlshack.com/execute-sql-task-in-ssis-sqlstatementsource-expression-vs-variable-source-type/

Why do you want to use .sql query file as a data source? As a text source or any other usage?

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

Answer 2

As a data source? A data source is generally the database server (or file or HTTP endpoint) that you want to read data from or write data to. So I don't believe that makes sense.

But if you simply want to use the query that you have in a file to retrieve data from a data source that SSIS is connected to then yes you can do that indirectly. To do it you'd need to read the file contents into a string variable in SSIS. Then use that string variable as your query in your source task instead of writing the query manually or using the designer. You may need to make some adjustments to the query to get it working inside SSIS though.

In my opinion, unless the query needs to change dynamically, then it would probably just be easier to copy the query from the SQL file and put it directly into SSIS. Perhaps you could even break up the query into separate tasks to make it easier to work with over time. That would be one less data point you have to keep around for your SSIS package to run.