Automate DACPAC refresh with PowerShell

I’m working on a project where we have recently started to use SSDT more and more. One feature I make use of, is Database References [http://schottsql.blogspot.co.uk/2012/10/ssdt-external-database-references.html], and use DACPACs as the source of these references. To keep this simple for the distributed development team and have one version of the truth, I keep the DACPACs under source control in TFS. After each successful release, I need to update the DACPACs by regenerating them and checking them back in. At first this was not too much of a hassle as a manual task, but as the list of database references has grown it’s become a chore.

And what do good DBAs do with chores? Automate them!

And what do good DBAs use to automate? Powershell!

The first thing you need is to get the TFS Power Tools, which give you the TFS PowerShell cmdlets that you need for managing changesets and checkins/outs. Make sure you get the right version for your environment – the link for VS2013 is here https://visualstudiogallery.msdn.microsoft.com/f017b10c-02b4-4d6d-9845-58a06545627f

 

#
# This script will refresh the DACPACs in the specified directory, 
#  checking them out from TFS and checking them back in.
#
##################################################################################

#Load the TFS powershell
Add-PSSnapin Microsoft.TeamFoundation.PowerShell

#Set which server to extract the DACPACs from
$ServerName = "TheBigBadServer"

#query this folder to get the dacpac file names
$filepath = "C:\DACPACs\"
$filelist = gci $filepath | where {$_.extension -eq ".dacpac"}

#each existing DACPAC will be refreshed
foreach ($filename in $filelist)
{
# parse out the database name, and full path name
$DatabaseName = $filename.BaseName
$outfilepath = $filename.FullName

# checkout
Add-TfsPendingChange -Edit -Item $outfilepath -Verbose -ErrorAction SilentlyContinue -wa 0

#Note: this path may be different on your PC, depending on which version of VS and/or SQL Server you are running
&"C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe" `
    /Action:extract `
    /SourceServerName:$ServerName `
    /SourceDatabaseName:$DatabaseName `
    /TargetFile:$outfilepath `
    /p:ExtractAllTableData=False `
    /p:ExtractApplicationScopedObjectsOnly=True `
    /p:IgnoreUserLoginMappings=False `
    /p:ExtractReferencedServerScopedElements=True `
    /p:IgnorePermissions=True `
    /p:IgnoreExtendedProperties=False `
    /p:VerifyExtraction=False

}

#Display which files will be checked in
Get-TfsPendingChange $filepath -recurse

# Commit all changes in one go
New-TfsChangeset -Item $filepath -recurse -Verbose -Comment "auto refresh DACPAC from PoSH"

This script expects existing DACPACs to be available in the directory, and that they are named database.dacpac
The DACPAC extraction options here are the same defaults that you get when you start the ‘Extract Data-tier Application’ window in SSDT/VS, but you can change these to suit : http://msdn.microsoft.com/en-us/library/hh550080

Leave a Reply

Your email address will not be published. Required fields are marked *