Deploy dacpac with Powershell on Build vNext

Deploy dacpac with Powershell on Build vNext.ps1

#
# Powershell script that deploys the dacpac to (localdb)\MSSQLLocalDB that the integration tests can run against.
#

$dbname = "xxx"
$dacpacname = "xxx.dacpac"

# Locate dacpac
$dacpac = gci -Recurse -Filter "$dacpacname" -ErrorAction SilentlyContinue | Select -First 1

# Load in Microsoft.SqlServer.Dac.dll (retrieved from nuget)
$localDirectory = (Get-Location).Path
Add-Type -Path "$localDirectory\packages\Microsoft.SqlServer.Dac.1.0.1\lib\Microsoft.SqlServer.Dac.dll"

# Create a DacServices object, which needs a connection string 
# The (localdb)\ProjectsV12 instance is created by SQL Server Data Tools (SSDT) and should not be used by applications
# (localdb)\MSSQLLocalDB is the SQL Server 2014 LocalDB default instance name
# (localdb)\v11.0 is the SQL Server 2012 LocalDB default instance name
$dacServices = New-Object Microsoft.SqlServer.Dac.DacServices "Data Source=(localdb)\MSSQLLocalDB;Integrated Security=True;Pooling=False"

# Options
$deployOptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$deployOptions.CreateNewDatabase = $true
$deployOptions.BlockOnPossibleDataLoss = $false
$deployOptions.BlockWhenDriftDetected = $false
# Must specify variables if there are any defined!
$deployOptions.SqlCommandVariableValues.Add("Environment", "dev")

# register event. For info on this cmdlet, see http://technet.microsoft.com/en-us/library/hh849929.aspx 
Register-ObjectEvent -InputObject $dacServices -EventName "Message" -Action { Write-Host $EventArgs.Message.Message } | Out-Null
 
# Load dacpac from file & deploy database
$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpac.FullName) 
$dacServices.Deploy($dp, $dbname, $true, $deployOptions)

# Print rows in tables (sp_MSForEachTable doesn't work so don't even try it)
# SQLCMD doesn't work against localdb
# sqlps doesn't exist on Build vNext instance
# sql credit http://stackoverflow.com/a/24119273
$sql = "SELECT t.name, s.row_count from sys.tables t JOIN sys.dm_db_partition_stats s ON t.object_id = s.object_id AND t.type_desc = 'USER_TABLE' AND t.name not like '%dss%' AND s.index_id = 1"
$connectionString = "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=dbnameIntegrated Security=True"
$sda = New-Object System.Data.SqlClient.SqlDataAdapter ($sql, $connectionString)
$sdt = New-Object System.Data.DataTable
$sda.fill($sdt) | Out-Null
$sdt.Rows