SSDT projects + integration/end-to-end tests + TearDown

Question

Let us say I have a sql server data tools project (SSDT) and a bunch of integration/end-to-end tests (deliberate not saying unit tests). Is there a way to TearDown and republish the existing test database via code from the test class library?

PS:

I am currently trying this (paths and file names simplified):

var p = new System.Diagnostics.Process
{
    StartInfo =
    {
    FileName =
        @"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe /Action:Publish /SourceFile:“D:\Database_20151114_18-23-29.dacpac” Profile:”D:\LocalDatabase.publish.xml”",
    UseShellExecute = true
    }
};
p.Start();

Unfortunately, I am getting:

{"The system cannot find the file specified"}

The dacpac and xml file is definitely there. Any ideas? Also, is it possible to create the latest dacpac file automatically?

score 1 · Answer 1 · edited May 23 '17 at 12:30

1

Assuming SSDT is installed on the machine running the tests, you can run sqlpackage.exe via a shell command in TearDown. sqlpackage.exe is what SSDT uses to publish a database.

[TearDown]
public void TearDown() {
   System.Diagnostics.Process p = new System.Diagnostics.Process();
   p.StartInfo.FileName = @"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe /Action:Publish /SourceFile:C:\DbProject\bin\Debug\DbProject.dacpac /TargetServerName:localhost /TargetDatabaseName:TestDb";
   p.StartInfo.UseShellExecute = true;
   p.Start();
}

See here for the sqlpackage.exe arguments. Note that sqlpackage.exe may be in a different path than the one I used above.

edited May 23 '17 at 12:30

Community

1
1

answered Nov 06 '15 at 18:13

Keith

20,636
11
84
125

I have SSDT installed locally but just cannot find sqlpackage.exe. Any ideas? – cs0815 Nov 14 '15 at 18:04
Ignore me, just found it. In my case: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin – cs0815 Nov 14 '15 at 18:12

cs0815 · Answer 2 · 2015-11-14T20:09:08.027

1

Something like this seems to do the trick:

const string dacPacFileName = @"D:\Bla.dacpac";
var connectionString = ConfigurationManager.ConnectionStrings["BlaConnectionString"].ConnectionString;

var dacPackage = DacPackage.Load(dacPacFileName);
var dacServices = new DacServices(connectionString);      
var dacOptions = new DacDeployOptions();
dacOptions.CreateNewDatabase = true;
dacServices.Deploy(dacPackage, "Bla", true, dacOptions);

However, it still does require the manual creation of a dapac file. Can this be automated as well?

edited Nov 14 '15 at 20:09

answered Nov 14 '15 at 19:02

cs0815

16,751
45
136
299

How to enable SSDT diagnostics when using DacServices object? DacDeployOptions does not seem to include that – user5855178 Aug 13 '22 at 16:40

score 1 · Answer 3 · answered Feb 16 '19 at 16:28

Yes it's absolutely possible to automate the whole process. It roughly goes like this:

Add a reference to the Microsoft.Build library to your end-to-end tests project
Build your test project from code using that library to get an up to date DacPac
Add a reference to the Microsoft.SqlServer.Dac library using either NuGet or by installing the SSDT tools
Use the Dac related classes and methods in that library to publish a fresh copy of the database at the start of each test using the DacPac the build created.

This excellent existing answer has all the details on how to do that.

SSDT projects + integration/end-to-end tests + TearDown

3 Answers3