SQL Azure and Powershell for DACPAC deploy

Question

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

Republican

Have you tried using the WindowsAzurePowerShell commandlets (downloadable through web platform installer, or on github at: github.com/windowsazure/azure-sdk-tools – Powered

The dacpac API and file schema were changed for SQl 2012 and are completely incompatible with previous versions of SQL as is the truth vice versa. Thus if you are using the SQL 2008 R2 dacpac DLLs with a 2012 SQL dacpac it will totally bomb – Rasputin

@Nick - Do you have any additional info on the incompatibility? – Smatter

Honestly it's been so long that I don't recall the details. I do remember the SQL Data Tools versioning in visual studio playing a part for myself in discovering the issue - Sorry. – Rasputin

Have you tried to run powershell ISE via an elevated command (as Administrator explicitly)? That user you are using might also need to have access to your Azure Deployment environment. – Solvolysis

The new DacFx APIs and SqlPackage.exe command line wrapper all support existing dacpacs exported using the old APIs, except one specific version (2.5, I believe). However new dacpacs use the Dac v3.0 format which is not understood by the old code. Per other comments SqlPackage.exe should be very simple to use, or if you want more control over logging you can use DacServices API directly.See msdn.microsoft.com/en-us/library/… – Levana

Answer 1

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

Topper

If you stumble upon this solution via a Google search like I did, you may have some trouble generating your publish profile. This can be done by executing Get-AzurePublishSettingsFile at a PowerShell command prompt. This will launch a web browser set to the Azure Publish Settings blade. You can download the publish profile from there. – Nonstriated

Answer 2

Try publishing with sqlpackage.exe and Publish.xml

Invoke-Expression = "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe /Action:Publish /Sourcefile:<.dacpac file_path> /pr:'<Publish.xml>'"

Tiu