SSDT - Track database changes from DACPAC deployment

1
votes

I'm using Visual Studio 2017 and TFS 2015 to create an SSDT project and deploy the DACPAC via TFS.

If I test the project in Visual Studio by publishing a profile, I'm able to view all the changes to the destination database (ex. Dropping Tables, etc) in the "Messages" tab.

I'm not seeing that same kind of output when I publish the DACPAC through TFS. There are timestamps when Deployment has started and finished, but no information on what is actually modified in the database.

Is there anywhere I can view a log of database changes that occurred when a DACPAC was published?

EDIT: I'm using a WinRM - SQL Server DB Deployment task in TFS Release Management to deploy the DACPAC.

2
tfs-2015sql-server-data-tools
How are you performing the publishing? "Through TFS" could mean any number of things. - Daniel Mann
@DanielMann, I added clarification to the question. - Britt Wescott
Short of adding in some sort of task to generate the script and/or the change report, I don't think there's an easy way to get this. I guess if you have all of the dacpacs in sequence, you could schema compare the prior one to the most recent one for a report/diff. Might also be missing something capturing that output, but I'm not familiar w/ TFS. We captured it in Jenkins without too many issues, but that's a different platform. - Peter Schott
Where is the DacPac file located [Local/target server] ? - Shalem
@Shalem, it's copied to the target server. - Britt Wescott

2 Answers

3
votes

You could add a command line task to use SqlPackage.exe tool to generate a difference report between database. The syntax is:

/Action:DeployReport /SourceFile:$dacpac-path /TargetConnectionString:$constr /OutputPath:$reportoutputfilepath

Useful links:

2
votes

There are several artifacts generated when you run deployment:

  1. DeploymentReport_X.txt --> This one has the steps performed
  2. your_project_name.publish.sql --> This is the actual script executed on source