How to format .runsettings file for SQL Server Unit Test

Question

I'm attempting to run SQL Server Unit Tests using the "Visual Studio Test" task in an Azure DevOps release pipeline. The tests keep timing out, and I believe it's because the pipeline cannot establish a connection to the database to be tested.

I would like someone review to review my .runsettings file and let me know if I am missing something.

I'm attaching screen shots so you can see how I configured everything. Thanks in advance to anyone who helps me with this.

<?xml version="1.0" encoding="utf-8"?>
<RunSettings>
  <configuration>
		<configSections>
			<section name="SqlUnitTesting" type="Microsoft.Data.Tools.Schema.Sql.UnitTesting.Configuration.SqlUnitTestingSection, Microsoft.Data.Tools.Schema.Sql.UnitTesting, Version=15.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
		</configSections>
		<SqlUnitTesting>
		  <DataGeneration ClearDatabase="true" />
		  <ExecutionContext Provider="System.Data.SqlClient" ConnectionString="Data Source={serverName}.database.windows.net;Initial Catalog={databaseName};User ID={login};Password={password};Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=600;Encrypt=False;TrustServerCertificate=True"
			  CommandTimeout="30" />
		  <PrivilegedContext Provider="System.Data.SqlClient" ConnectionString="Data Source={serverName}.database.windows.net;Initial Catalog={databaseName};User ID={login};Password={password};Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=600;Encrypt=False;TrustServerCertificate=True"
			  CommandTimeout="30" />
		</SqlUnitTesting>
		<startup>
			<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2"/>
		</startup>
	</configuration>
</RunSettings>

6.3.1

Answer 1

Hi DataJedi,

Thank you for posting here.

For running sql unit test into azure devops pipeline, we recommend you could refer this blog:

Securely configuring Azure DevOps pipeline for SQL unit testing

SQL Server Database Unit Testing in your DevOps pipeline

And as far as I know, we could not preview files in pipeline in azure devops. So to review runsettings file, please check if your project has uploaded to azure, then you could go to Repos/files to read and edit runsettings file.

Hope it could help you.

Best Regards,

Dylan

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]

Answer 2

That first link looks promising. The tests work when I have the connection string hard-coded in the app.config. If I can use variables there it may resolve to issue. Will let you know after I have a chance to try it. Thanks.

Answer 3

Hi DataJedi,

I'm running into the same issue. Do you have any update you can share?

Thanks,

Jon

Answer 4

Hi DataJedi,

I'm running into the same issue. Do you have any update you can share?

Thanks,

Jon

Actually, I do have an update. I took a somewhat different approach than Dylan Zhu.

First, please note that your test project will have a file named "{testProject}.dll.config”. This is a copy of your “app.config” file created when the test project is built.

I created multiple copies of the config file (one per environment I am deploying too), and placed them in a new folder I created within my test project. Each file is named "{testProject}_$environment.config". The folder and its contents are checked in to TFS.

I then modified the build to add two “Copy Files” tasks. The first one copies the files containing the tests (“{testProject}.dll”, “{testProject}/bin/debug”, “SqlDatabaseSetup.cs” and all the *.cs and *.resx files from “$(System.DefaultWorkingDirectory)” to “$(Build.ArtifactStagingDirectory)/ {testProject}”. The second one copies my .config files from “$(System.DefaultWorkingDirectory)” to “$(Build.ArtifactStagingDirectory)/ {testProject}/Config”. Make sure you check the box for “Flatten Folders” so that you have a shorter path to work with.

Now I went to the Release pipeline and added a “PowerShell” task to copy the appropriate .config file to the Drop folder. I used PowerShell because it made it much easier to reference the build variables.

Here is the script:  

$environment = "{solutionName}-$(Release.EnvironmentName)".ToLower()
$file = "{testProject}_$environment.config"
$sourcePath = "$(System.DefaultWorkingDirectory)\$(artifactName)\drop\{testProject}\Config\{testProject}_$environment.config"
$destinationPath = "$(System.DefaultWorkingDirectory)\$(artifactName)\drop\{testProject}\{testProject}.dll.config"
Write-Host "Environment = $environment"
Write-Host "File = $file"
Write-Host "SourcePath = $sourcePath"
Write-Host "DestinationPath = $destinationPath"
Copy-Item -path $sourcePath  -destination $destinationPath

Note that “$(artifactName)” is a custom build variable used in our build and release pipelines.

After implementing these changes, we were able to run the database tests.

The hardest part was figuring out how to map the physical paths I saw in the build and release logs to the TFS Build Variables. I figured it out by temporarily adding some “Command Line” tasks to execute “dir” commands. For example, I had a task to execute “dir $(Build.ArtifactStagingDirectory)/{testProject}/Config”. This allowed me to check the logs and see the physical path.

Hope this helps you (and others).