SQL Azure and Powershell for DACPAC deploy

azure-powershell, azure-sql-database, dac, powershell, sql-server

Solution

If you installed latest SSDT 2015 (https://msdn.microsoft.com/en-us/mt186501). I highly recommended it because it has a lot of useful options for the Publish profile, which can be generated using Visual Studio.

You can use the following to publish your file to Azure using Powershell

$sqlpackage = "C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe"

$dbserver = "<Azure DB Location>"  
$database = "<name of DB on Server>"

# UNC Paths

$dbProfile = "<Path to your Publish Profile>"
$mydacpac = "<location of the dacpac>" 

# Publish Command

& $sqlpackage /Action:Publish /tsn:$dbServer /tdn:$database /sf:$mydacpac/pr:$dbProfile /variables:myVariable=1

PS : & works like Invoke-Expression mentioned by Pradebban

Problem

I am deploying a dacpac built on visual studio 2012 to SQL Azure using powershell and running into issues which I think could be related to some version incompatiblity. The publish works fine when I do it from visual studio but throws an exception when I do it using powershell. Here is what I am doing in Powershell ``` [System.Reflection.Assembly]::Load("Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") | out-null [System.Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") | out-null [System.Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") | out-null [System.Reflection.Assembly]::Load("Microsoft.SqlServer.Management.Dac, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") | out-null Trap { PrintException($_.Exception); $fileStream.Close() return; } $sqlServerFullName = $sqlServerName + ".database.windows.net" $serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($sqlServerFullName, $adminLogin, $admingPwd) $serverconnection.Connect() $dacstore = New-Object Microsoft.SqlServer.Management.Dac.DacStore($serverConnection) $fileStream = [System.IO.File]::Open($dacpacPath,[System.IO.FileMode]::OpenOrCreate) Write-Host "Reading contents from $dacpacPath..." $dacType = [Microsoft.SqlServer.Management.Dac.DacType]::Load($fileStream) ``` The last line in the code above is what is crashing with the following error (inner exception value) and not proceeding further ``` The stream cannot be read to construct the DacType. There is an error in XML document (2, 2). <DacType xmlns='http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/0 2'> was not expected. ``` Here is the $PSVersionTable from powershell ISE that I am using ``` Name Value ---- ----- PSVersion 2.0 PSCompatibleVersions {1.0, 2.0} BuildVersion 6.1.7601.17514 CLRVersion 4.0.30319.17929 WSManStackVersion 2.0 PSRemotingProtocolVersion 2.1 SerializationVersion 1.1.0.1 ``` Wondering what could cause this issue when I deploy using Powershell when it works fine when I deploy it using visual studio 2012 P.S. - The powershell deploy works fine using the same script with a dacpac file that I found on the internet which was apparently targeted for a SQL 2005 version

Original source