How to Execute .SQL files by using SSIS Package - SSIS Tutorial

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