CODEWITHSUNDEEP
t-sqlpowershellautomation
Michelle Santos
Michelle Santos

Reputation: 267

Powershell: How do I execute multiple txt files on SQL Server

I'm slowly learning to utilize Powershell and as a newbie please be easy on me. I created a Powershell script that would generate an insert statement from a number of excel files and save it into individual text files. How could I utilize Powershell to execute the text files on SQL Server?

Upvotes: 0

Views: 1531

Answers (2)

Michelle Santos
Michelle Santos

Reputation: 267

Your comment was indeed very helpful. Since I don't have GO Statements on my txt files I made use of this method which I derived from yours @ShawnMelton

#Get list of files
$Files = Get-ChildItem 'C:\Users\Me\Documents\Test\*.txt'


try {
    $Filename = $File.BaseName 

    foreach ($file in $files) {

    Invoke-sqlcmd -ServerInstance "ServerName" -Database "Test" -InputFile $file -ErrorAction Stop 
    Write-Host -ForegroundColor Green "EXECUTED SuCCESSFULLY: " $filename 
    Move-Item $File "C:\Users\Me\Documents\Test\Executed"
 }
}
catch {
 Write-Host -ForegroundColor RED "FAILED TO EXECUTE: " $filename
}

To avoid confusion I transferred all successfully executed files on a seperate folder. Therefore all unexecuted and failed to execute script would be left on the main folder.

Upvotes: 0

user847990
user847990

Reputation:

It will be based on the queries you have in the text files. If they have the GO transaction terminator, then you are limited to using SMO for this because the .NET client System.Data.SqlClient will not accept that command as good syntax. It is strictly a terminator for SSMS and not true SQL syntax.

I in fact had the request for a client to handle the GO so you could do something like this:

$dbServer = MyServer
$ScriptDirectory = 'C:\scripts'
Import-Module SQLPS
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $dbServer

try {
 $file = dir $ScriptDirectory
 foreach ($f in $file) {
  $s = Get-Content $f -Raw
  $srv.ConnectionContext.ExecuteNonQuery($s);
 }
}
catch {
 $error[0].Exception
}

If you do not have to deal with the GO statement and want to use the .NET method you can check out an article published on SQLShack. It walks you through how to make the connection and execute, and also touches a bit more on using PS with SQL Server.

Upvotes: 2

Related Questions