SSIS: How do I create tables using Foreach Loop Container?

Question

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.

• Evandro: Hi, are all the files with the same column specification? The scripts are inside each file?
• Fayçal Salhi: each file contains a script that would create a table. But each tables has different columns
• Shooter McGavin: 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.

Answer 1

• 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

Answer 2

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
 #$scriptName 
 #$script
 $subj = "subject=$scriptName"
 Invoke-Sqlcmd -ServerInstance $SQLServer_stage -InputFile $script
 }