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

Ali, Ahmad 21 Reputation points
2021-09-22T20:40:56.74+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
0 comments
Accepted answer
  1. ZoeHui-MSFT 32,426 Reputation points
    2021-09-23T02:31:31.373+00:00

    Hi AliAhmad-4517，

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

    134290-screenshot-2021-09-23-102843.jpg

    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

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Michael Taylor 47,716 Reputation points
    2021-09-22T21:27:24.703+00:00

    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.

    0 comments