Errors when creating dacpac

• Scott In Sydney

  Hall of Fame

  Points: 3331

  More actions

  March 18, 2019 at 12:37 am

  #377770

  I'm using SSMS to create a dacpac:  RMB database --> Tasks --> Extract Data-tier Application.

  I'm getting these errors (excerpt):

  
Validation of the schema model for data package failed.
Error SQL71501: Error validating element [dbo].[SYSNAME]: Parameter: [dbo].[ScriptCreateTableKeys].[@table_name] has an unresolved reference to Built-in Type [dbo].[SYSNAME].
Error SQL71501: Error validating element [dbo].[SYSNAME]: Parameter: [dbo].[ScriptDropTableKeys].[@table_name] has an unresolved reference to Built-in Type [dbo].[SYSNAME].

  Error SQL71561: Error validating element [dbo].[udf_DeltaExtractFacility]: Function: [dbo].[udf_DeltaExtractFacility] has an unresolved reference to object [hierep].[dbo].[FACILITY].
Error SQL71561: Error validating element [dbo].[udf_DeltaExtractFacility]: Function: [dbo].[udf_DeltaExtractFacility] has an unresolved reference to object [hierep].[dbo].[FACILITY].[facility_identifier].
Error SQL71561: Error validating element [dbo].[udf_DeltaExtractFacility]: Function: [dbo].[udf_DeltaExtractFacility] has an unresolved reference to object [hierep].[dbo].[FACILITY].[replica_valid_from_date].
Error SQL71561: Error validating element [dbo].[udf_DeltaExtractFacility]: Function: [dbo].[udf_DeltaExtractFacility] has an unresolved reference to object [hierep].[dbo].[FACILITY].[replica_current_record].
Error SQL71561: Error validating element [dbo].[udf_DeltaExtractFacility]: Function: [dbo].[udf_DeltaExtractFacility] has an unresolved reference to object [hierep].[dbo].[FACILITY].[replica_valid_to_date].

  <rest deleted>


  1.  How can there be an unresolved reference to the Built-in Type [dbo].[SYSNAME]?

  2.  [dbo].[udf_DeltaExtractFacility] is using a 3-level name to another database and table on the same server.  The [hierep].[dbo].[FACILITY] table exists, the [hierep].[dbo].[FACILITY].[facility_identifier] column exists (as well as the other columns), and the UDF runs fine without error.

  3.  On another database (output not shown), dacpac generation fails whenever I'm using a synonym to use a 2-level name back to this same database.  Again, the views, UDF's, etc. all run fine without error.

  Any advice on resolving these issues in generating a dacpac?  I need this dacpac as a database reference for a SQL Server Database Project.

• HandyD

  Mr or Mrs. 500

  Points: 507

  More actions

  March 19, 2019 at 10:53 pm

  #2024967

  I've had nothing but trouble using SSMS to extract the DACPAC/BACPAC of databases. It has all these weird reference errors that prevent extracting the DACPAC, you can hit similar issues referencing system views and things like INFORMATION_SCHEMA. Once you load it into Visual Studio as a Project you can add references that clear up these issues.

  Try using SqlPackage.exe to perform the extraction and make sure /p:VerifyExtraction=False is used. This should prevent verification of the schema after the extraction. SSMS uses SqlPackage.exe as well, but sets this value to True.

  More info: https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-2017