Scenario:
Sometime we have requirement to execute .sql files in SSIS. Let's assume that we have some .sql files in one of the folder those contain different DDL Statements, DML ( update, delete etc.) statements.We need to loop through all .sql files and run from the folder.
Solution:
Step 1:
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 |
Step 2:
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 |
Step 3:
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 |
Step 4:
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.
Video Demo : How to execute dot(.) sql files from a folder in SSIS Package
Execute multiple .SQL files from a folder in SSIS Package