I appreciate that SSIS is a good tool for this situation, but I'm in kind of a weird environment where it's not accessible, on top of me not liking it, so it's not just a matter of preference.

I can use PowerShell to kick out some SQL based on file names, which is essentially what I've done:

Get-ChildItem C:\Users\$env:username\Desktop\$mydir |

Where-Object { $_.Name -match "^\w+.txt$" } |

Foreach-Object { $dbn = $_.Name.Substring(0,6)

$table = $_.Name -replace '.txt', ''

$altr = "alter table $schema.s_$table add column dbn varchar (100) default '$dbn';`n`n" |

Out-File -FilePath C:\Users\$env:username\Desktop\SQL\$schema\Alter.sql -Append -Encoding default }

So after I load all my files, I run this and it fills in the information for me. When I add it before loading, it does not populate.

Of course, now I'm running into an issue where SQL thinks some reports have more columns in them than they actually do. Ho-hum. 😛