I got a bunch of .sql files each containing a script that would create a certain table.

I want to create tables using these scripts in said files (each table to be created using one file).

I got a foreach loop container that specifies the path and which files to use.

I don't know how to configure the Execute SQL Task inside the foreach loop container to execute the script in each one of these files in order to create the tables.

  • Hi, are all the files with the same column specification? The scripts are inside each file?
    – Evandro
    May 16, 2018 at 7:51
  • each file contains a script that would create a table. But each tables has different columns May 16, 2018 at 7:57
  • This is a bit of a strange approach. Normally the table structure of the database wouldn't be altered during an ETL operation. What are you actually trying to do here? The issue isn't that you won't be able to do what you intend. It's that once the tables are created, using them in SSIS, with dynamic table names and column names/orders/data types will be damn near impossible. So more context on what you are actually trying to accomplish might help. May 16, 2018 at 15:00

2 Answers 2

  • Set the Foreach Loop as a Foreach File Enumerator pointed at your .sql files
  • Create a variable and map the Fully qualified file name to it
  • Set the Execute SQL Task's SQLSourceType to File connection, and create a new file connection for it
  • Set the Connection String expression on the file connection to your file name variable

This isn't exactly what you're looking for, but I do something similar with powershell. We have an automated process where we have to dynamically build a folder name and UNC path and execute every SQL script in that path

Here is most of the script I have for this. Pretty easy to get the rest to build your file paths or I can post it too.

$scriptlocation = $fs + $root_loc + $folder_name
$Server = $SqlConnection_test.DataSource
$scripts = Get-ChildItem -literalpath $scriptlocation | Where-Object 
      {$_.Extension -eq ".sql"} | Sort-Object
        foreach ($s in $scripts)
    ##Write-Host "Running Script : " $s.Name -BackgroundColor DarkGreen - 
 ForegroundColor White
    $script = $s.FullName
    $scriptName = $s.Name
    $subj = "subject=$scriptName"
    Invoke-Sqlcmd -ServerInstance $SQLServer_stage -InputFile $script

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.