This SSIS Integration Services article shows how to Execute SQL Tasks using the File Connection, which helps to execute queries within the file system.
The below sql file shows the query that creates a table (if it does not exist) and inserts a few records into it.
Drag and drop Execute SQL Task onto the control flow region.
Double-click to open the editor. In this SSIS example, we chose the existing Task Results Connection. However, you can create a new one.
Change the SQL Source Type to File connection. Please refer to the Execute SQL Task Properties article.
Click the New Connection hyperlink to open the File Connection Manager Editor. Use the Browse button to choose the sql file for the local hard drive.
Click OK to close the Execute SQL Task editor.
Run the SSIS Execute SQL Task using the File Connection Package.
Open the SQL Management Studio to view the newly created table from the SQL file and the inserted records.
Let me edit the file and rerun the package.
Table result.
