Error deploying SQL database using powershell script

Legacy Uncategorised
1

Hi ,
I am getting following error while deploying an SQL database dacpac application using octopus.

*** Could not generate deployment script.

15:04:02

Error
Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql120DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service.

15:04:02

Error
Script ‘C:\Octopus\Applications\Staging\Database\2.0.0.0\Deploy.ps1’ returned non-zero exit code: 1. Deployment terminated.

15:04:02

Error
Running rollback conventions…

15:04:02

Error
Script ‘C:\Octopus\Applications\Staging\Database\2.0.0.0\Deploy.ps1’ returned non-zero exit code: 1. Deployment terminated.

15:04:02

Error
The remote script failed with exit code 1

SCRIPT USED IS :

$dbName = $OctopusParameters[‘DbName’]
$user = $OctopusParameters[‘DbUser’]
$pwd = $OctopusParameters[‘DbPassword’]
$server = $OctopusParameters[‘DbServerName’]

Write-Host DbName: $dbName
Write-Host User: $user
Write-Host Server: $server

& ‘C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe’ /Action:Script /OutputPath:Upgrade.sql /SourceFile:Database.dacpac /TargetServerName:$server /TargetDatabaseName:$dbName /p:IncludeCompositeObjects=true /TargetUser:$user /TargetPassword:$pwd

2

Hi Pallavi,

Thanks for getting in touch.

Is this problem occurring only from within Octopus? Are you able to deploy the DAC package from the windows command line using SqlPackage.exe?

If it works from the command line outside of Octopus, you can then check via the Octopus Script Console and see if it functions differently from within Octopus. If it only occurs from within Octopus, can you please send a full deployment log, along with the version of SQL installed on the source and target machines (please also let us know if there are multiple versions of SQL installed on the source or target machines).

There is also a step template provided by the community for deploying DAC packages that you may be interested in here. It would be worth giving this step template a try and see if it behaves the same way.

Let me know how you go.

Cheers
Mark S.