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 SSIS 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.
- SSIS Execute SQL Task Single Rowset
- SSIS Execute SQL Task Full Row Set
- Execute SQL Task in SSIS Result set None
- SSIS Execute SQL Task to run Multiple Query Files
Click the New Connection hyperlink to open the SSIS 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.
