This SSIS Integration Services article shows how to run multiple query files using an Execute SQL Task and the Foreach loop File enumerator with an example.
To demonstrate this, we use the below-shown sql files; our job is to run all of them.
SSIS Execute SQL Task to run Multiple Query Files
Drag and drop the Foreach loop container into the SSIS control flow region. Next, we created two variables, FileName and FolderPath, of string data type to store the file names and folder path.
Double-Click on it will open the editor. First, change the Enumerator type to Foreach File Enumerator. Next, click the browse button beside the Expression.
Choose Directory as the Property under the Property Expressions Editor window and click the Expression. Then, drag User::FolderPath to Expression within the Expression Builder and click OK.
Click Ok to Close the Property Expressions Editor window.
In this example, we want to load all the sql files. So, use *.sql and select the Name and extension option to get the name along with the extension.
Under the Variable mappings tab, select the User::FileName variable. It assigns all the sql file names to this User::FileName variable.
Add SSIS Execute SQL Task to Foreach loop container to run Multiple Query Files.
Double-click to open the editor. Here, we chose the existing Connection, but you can create a new one. Next, change the SQL Source Type to File Connection and click the <New Connection…> button to open the File Connection Manager Editor.
Use the Browse button to choose any of the sql files for the local hard drive.
Click OK to close the Execute SQL Task editor.
Select the SQL Connection and Go to the Properties window. Next, click the browse button beside the Expressions.
Choose the ConnectionString as the Property Click the Expression and write the Expression. Here, we build the file path by combining or concat the FolderPath and FileName variables.
Click Ok to Close the Property Expressions Editor window.
Run the SSIS Multiple query Files using the Execute SQL Task and Foreach Loop Enumerator package.
Open the Management Studio to check the SQL table result.
