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

Have you tried using the WindowsAzurePowerShell commandlets (downloadable through web platform installer, or on github at: github.com/windowsazure/azure-sdk-tools — Mark Cowlishaw - MSFT
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 — Nick Nieslanik
@Nick - Do you have any additional info on the incompatibility? — DonBecker
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. — Nick Nieslanik
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. — WickedFan

Vinay Vinay · Accepted Answer · 2015-07-24T17:56:30

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

SQL Azure and Powershell for DACPAC deploy

2 Answers