First of all we have to use For-each loop so we can loop through these files. Configure you For-each loop as show in figure below.
|How to execute dot SQL files from a folder in SSIS Package - SSIS Tutorial|
Then click on Variable Mapping and create a new variable to save the file name.
|Add variable in For-Each Loop container to save .sql file name - SSIS Tutorial|
Drag Execute SQL Task to Control Flow Pane and then configure as shown in Figure below. As we are using the .sql file to run , so instead of direct input we used file connection and then made connection to one of the .sql file.
|Create connection to one of the dot SQL file and choose Connection Manager on which you would like to run .sql files|
As we need to loop through the files in folder and run each of the .sql file.We will be using expressions on connection manager for file. Each time the For-each Loop will pick one file and then connection will be updated and .sql will be executed.
|Write expression to update connection manager path for new file on each iteration of For-each Loop Container|
Your SSIS Package is ready and it should be able to execute any number of Dot SQL files from a folder for you. Go ahead and execute and see if all works as expected.