Tool to Execute Multiple SQL Scripts – VS 2013 Release Management – Part 1

Is it possible to execute set of SQL scripts downloaded to Deployment Agent in VS 2013 Release Management? Yes. But this requires a custom tool and an action.
Let’s see how we can do this step by step.
As the first step write a PowerShell script capable of executing batch of scripts in a transaction.

 Param([string]$ServerInstance, [string]$DatabaseName, [string]$ScriptPath)   
  $ErrorOccured = $false   
  #Executing following snapins to Invoke-SqlCmd    
  Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue   
  Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue   
  Write-Host "Executing patch scripts of path: $PatchScriptsPath"   
  Start-Transaction -RollbackPreference Error   
  Use-Transaction -TransactedScript {    
   foreach ($file in Get-ChildItem -path $ScriptPath -Filter "*.sql")   
   {    
    Write-Host Executing: $file.name ...   
    $ScriptPath = $ScriptPath + "\" + $file.name   
    Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $DatabaseName -InputFile $ScriptPath -ErrorAction SilentlyContinue -ErrorVariable errors   
    foreach($error in $errors)   
    {   
     if ($error.Exception -ne $null)   
     {   
      $ErrorOccured = $true   
      Write-Host -ForegroundColor Red "Exception: $($error.Exception)"   
     }   
    }   
   }   
  } -UseTransaction    
  if ($ErrorOccured)   
  {   
   Undo-Transaction   
   throw "Error occured while Executing SQL Scripts."   
  }   
  else   
  {   
   Complete-Transaction   
   Write-Host Successfully executed all SQL scripts.   
  }  

Next create a tool in Release Management Client as shown below.



Parameters are

Name	Type	Description
ServerInstance	Standard	SQL Server Name with Instance Name
DatabaseName	Standard	Name of the database the scripts should run
ScriptPath	Standard	Location of the scripts to be executed

Create an action as shown below using the tool created above.



This action can be used in a release template as shown below to execute SQL scripts in a transaction.





On failure scripts actions will not be committed and release management action will fail. In Part 2, I will show this tool in action.