How to create an SSIS Package that will run a list of T-SQL queries located in a specific folder?

Question

I am using SQL Server 2012 and I need to create an SSIS package that will run a list of T-SQL queries from a specific folder.

My list of T-SQL queries have been named starting with '01 (name of query).sql' and the folder contains a list of 25 queries. I need the task to run these queries starting with query '01...' to '25...'

Can this be created in an SSIS package? If yes, which task should I use?

Update 1

I tried the first method suggested by @Hadi and the script task throws the following exception

Error: Exception has been thrown by the target of an invocation

screenshot of my task in SSDT

Script Task VB code

 <Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

 Public Sub Main()

    Using sr As New IO.StreamReader(Dts.Variables("strFilename").Value.ToString)

        Dts.Variables("strQuery").Value = sr.ReadToEnd()
        sr.Close()

    End Using

    Dts.TaskResult = ScriptResults.Success
End Sub

End Class

Variables

Answer 1

(1) Using Script Task + Execute SQL Task

1. Add a Foreach Loop container
2. Choose the type = File enumerator
3. Select the Folder containing the .sql files as Source Directory
4. Select the Fully qualified file name option
5. Go To variable mapping Tab, Map the result to a Variable (Create new one ex: @[User::strFilename])
6. Create a Variable of Type String @[User::strQuery]
7. In the foreach Loop container add a Script Task and Select @[User::strFilename] as ReadOnly Variable and @[User::strQuery] as ReadWrite Variable

8. Inside the Script Task Write the following Code (Choose Visual Basic as Language):

   Try
   
       Using sr as new IO.StreamReader(Dts.Variables("strFilename").Value.ToString)
   
           Dts.Variables("strQuery").Value = sr.ReadToEnd()
           sr.Close()
   
       End Using
   
       Dts.TaskResult = ScriptResults.Success
   
   Catch ex as exception
   
       Messagebox.Show(ex.Message)
       Dts.TaskResult = ScriptResults.Failure
   
   End Try
   

9. Add an Execute SQL Task linked to the Script Task, and Select the ConnectionString property, then choose SQLSource Type property = Variable

10. Select @[User::strQuery] as SourceVariable

---

(2) Using Execute Process Task

Use a foreach loop container to loop over these files, then use an Execute process task to run these queries using SQLCMD.

Additional Information:

• SSIS BASICS: Introducing the Foreach Loop Container
• Foreach Loop Container
• Run Transact-SQL Script Files Using sqlcmd

---

(3) Using a batch file

Also you can achieve this without SSIS, just using a batch file .BAT that you create with notepad:

• Run all SQL files in a directory

Answer 2

I upvoted Hadi, as I learned something new.

But this is how I do it with a script task. Dealers choice.

//Read SQL to string
System.IO.StreamReader sr = new System.IO.StreamReader(Dts.Variables["ForEachFilePathName"].Value);
string sql = sr.ReadToEnd();
sr.Close();

string cstr = Dts.Variables["connString"].Value;
using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(cstr))
{
   System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);

     conn.Open();
     cmd.ExecuteNonQuery();
}