How to Integrating tSQLt tests with Visual Studio Team Services?

Question

I had developed simple Ready Roll database project by following this Getting started with ReadyRoll.

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

Next check in my code into VSTS and created the build and release steps by following this links.

create-vsts-tfs-build

create-vsts-tfs-release

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

Run tSQLt tests task configuration at release level

I am little bit confusion which path to give under Test results output path in in Run tSQLt tests task configuration at release level.

So, I am getting the error at release level in Run tSQLt tests task like shown in below figure.

Can anyone please tell me how to resolve the above issue?

Answer 1

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

The problematic 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"

were 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"""

Apologies for any inconvenience caused.

Answer 2

There is the issue in Run tsSQLt Tests task (can reproduce this issue).

The source code (can find it in agent working folder, 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];

I can run the test by calling "sqlcmd.exe" -S v-tinmo-12r2 -d ReadyRollDemo2 -U starain -P User@123 -i RunTests.sql command (remove single quotes from 'RunTests.sql', also can replace single quotes to double quotes))

So, you can run test and get reports by calling sqlcmd.exe through command line tasks or you can refer to the source code of Run tSQLt Tests task to custom the build/release task.