I'm trying to deploy database changes to a Windows server using an Azure release pipeline. I'm using a DACPAC file with table data included.

This is my code to generate the DACPAC which I run in a build pipeline:

sqlpackage `
    /Action:Extract `
    /SourceConnectionString:"Data Source=localhost;Initial Catalog=Shipping;Integrated Security=False;Persist Security Info=True;User ID=username;Password=password" `
    /TargetFile:"$PublishDir/Shipping.dacpac" `
    /p:IgnoreUserLoginMappings=True `
    /p:VerifyExtraction=False `
    /p:ExtractAllTableData=True

In the release pipeline I have a deployment group job that contains the following steps:

1. Disable triggers
2. Disable constraints
3. Deploy DACPAC
4. Enable triggers
5. Enable constraints

I use the SQL Server Deployment task for the Deploy DACPAC step. Every time it gets to that step this error is thrown:

*** Could not deploy package.

Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 208, Level 16, State 1, Procedure vGetTransportPackages, Line 31 Invalid object name 'FreightDataStore.TP.TransportPackage'.

Error SQL72045: Script execution error.  The executed script:
 CREATE VIEW [fds].[vGetTransportPackages] AS WITH PACKAGES AS (SELECT DISTINCT tp.transportPackageID AS TransportPackageID, tp.tpID AS CarTransportPackageNumber, tp.airWayBillNo AS AirwayBillNumber, tp.pod AS DestinationPort,
(SELECT TOP 1 tl.LocationName FROM fds.TransportLocation AS tl WHERE  tl.UnLocCode = tp.pod) AS DestinationPortName, pod.countryCode AS DestinatonPortCountry, (SELECT TOP 1 c.CountryName FROM   ref.Countries AS c WHERE  c.CountryCode = pod.countryCode) AS DestinationPortCountryName, tp.poe AS OriginPort, (SELECT TOP 1 tl.LocationName FROM   fds.TransportLocation AS tl WHERE  tl.UnLocCode = tp.poe

I'm relatively new to database deployment. Do I need to change the way I generate the DACPAC?

CodePudding user response：

I figured out the issue. I didn't install the other databases on the server that my Shipping database depends on. Once I installed FreightDataStore and a few other databases I was able to deploy the DACPAC successfully.