How to integrate tSQLt tests with Visual Studio Team Services?

Question

I developed a simple Ready Roll database project by completing the following Getting Started with ReadyRoll .

After that, I added tSQLt tests in VS2015 using the tSQLt adapter by doing this Running tests in ReadyRoll with the tSQLt adapter .

Then test my VSTS code and create build and release steps by following these links.

create-vsts-tfs-build

create-vsts-tfs-release

tsqlt-tests-with-visual-studio-team-services

Run tSQLt tests at release level

I am a little confused which way to give as a result of the output path of the test results in the section “Running tSQLt” tests the configuration of the task at the release level.

So, I get an issue at the release level in the "Run tSQLt" task, as shown in the figure below.

Can anyone tell me how to solve the above problem?

Answer 1

This issue is now fixed in version 2.1.4 of the VSTS ReadyRoll extension .

Problem lines:

Invoke-VstsTool -FileName 'sqlcmd.exe' -Arguments "-S $databaseServer -d $databaseName -i 'RunTests.sql'"
Invoke-VstsTool -FileName 'sqlcmd.exe' -Arguments "-S $databaseServer -d $databaseName -i 'OutputResults.sql' -o $outputPath"

have been replaced by:

$connectionParams = "-S ""$databaseServer"" -d ""$databaseName"""
Invoke-VstsTool -RequireExitCodeZero -FileName 'sqlcmd.exe' -Arguments "-b $connectionParams -i RunTests.sql"
Invoke-VstsTool -RequireExitCodeZero -FileName 'sqlcmd.exe' -Arguments "-b $connectionParams -i OutputResults.sql -o ""$outputPath"""

Sorry for any inconvenience.

Answer 2

There is a problem in the Task tsSQLt Tests task (it can reproduce this problem).

Source code (you can find it in the working folder of the agent, for example: _work / _task / RunDatabaseTestsxxx):

RunDatabaseTests.ps1:

[CmdletBinding(DefaultParameterSetName = 'None')]
param()
$global:ErrorActionPreference = 'Stop'
Import-Module -Name "$PSScriptRoot\ps_modules\TaskHelpers"

[string]$outputPath = Get-VstsInput -Name OutputPath
[string]$databaseServer = Get-VstsInput -Name DatabaseServer
[string]$databaseName = Get-VstsInput -Name DatabaseName
[bool]$useWindowsAuth = Get-VstsInput -Name UseWindowsAuth -AsBool
[string]$databaseUserName = Get-VstsInput -Name DatabaseUserName
[string]$databasePassword = Get-VstsInput -Name DatabasePassword

Write-VstsTaskVerbose -Message 'Ensuring the path to sqlcmd is present in env:PATH'
Initialize-SqlCmdInPathEnvironmentVariable

[string]$sourcesDirectory = Get-VstsTaskVariable -Name 'Build.SourcesDirectory'

if(!$sourcesDirectory)
{
  Write-VstsTaskVerbose -Message 'Build.SourcesDirectory was not found as a VSTS Task variable'
  Write-VstsTaskVerbose -Message 'Looking for Agent.ReleaseDirectory to use as source directory instead'
  # For RM, look for the test assemblies under the release directory.
  $sourcesDirectory = Get-VstsTaskVariable -Name 'Agent.ReleaseDirectory'
}

if(!$sourcesDirectory)
{
  $msg = 'No source directory found'
  Exit-WithError $msg
}

Write-VstsTaskVerbose -Message 'Source directory found'
Write-VstsTaskDebug -Message "buildSourcesDirectory = $buildSourcesDirectory"

if ([System.IO.Path]::IsPathRooted($outputPath) -eq $False)
{
  $outputPath = Join-Path $sourcesDirectory $outputPath
}

Write-VstsTaskDebug -Message "OutputPath(absolute) = $outputPath"

if ($useWindowsAuth -eq $True) {
  Write-VstsTaskVerbose -Message 'Starting sqlcmd with Windows Authentication'
  Invoke-VstsTool -FileName 'sqlcmd.exe' -Arguments "-S $databaseServer -d $databaseName -i 'RunTests.sql'"
  Invoke-VstsTool -FileName 'sqlcmd.exe' -Arguments "-S $databaseServer -d $databaseName -i 'OutputResults.sql' -o $outputPath"
}
else {
  Write-VstsTaskVerbose -Message 'Starting sqlcmd with SQL Authentication'
  Invoke-VstsTool -FileName 'sqlcmd.exe' -Arguments "-S $databaseServer -d $databaseName -U $databaseUserName -P $databasePassword -i 'RunTests.sql'"
  Invoke-VstsTool -FileName 'sqlcmd.exe' -Arguments "-S $databaseServer -d $databaseName -U $databaseUserName -P $databasePassword -i 'OutputResults.sql' -o $outputPath"
}

# sqlcmd limits line length to 2034 characters and inserts new lines. Remove these.
$x = Get-Content $outputPath -Raw
$x.Replace("`r`n", "") > $outputPath

RunTests.sql:

EXEC [tSQLt].[RunAll];

OutputResults.sql:

:XML ON
EXEC [tSQLt].[XmlResultFormatter];

, "sqlcmd.exe" -S v-tinmo-12r2 -d ReadyRollDemo2 -U starain -P User@123 -i RunTests.sql ( "RunTests.sql", ))

, , sqlcmd.exe , " tSQLt Tests", /.