Error SQL71501 when Rebuilding new dacpac from an existing dacpac

Question

I have a requirement to merge multiple dacpacs into a single dacpac so using the example (http://dacsamples.codeplex.com), when I attempt to build using Microsoft.SqlServer.Dac.Extensions.dll - DacPackageExtensions.BuildPackage() method, I get errors to do with unresolved reference to unnamed objects in various locations. However if I build using VS2015 IDE then I get no such errors, presumably it's the same thing? Any ideas?

Microsoft.SqlServer.Dac.DacServicesException was unhandled
HResult=-2146233088 Message=Cannot save package to file. The model has build blocking errors: Error SQL71501: Error validating element [lr].[Sample]: View: [lr].[Sample] has an unresolved reference to unnamed object. Error SQL71501: Error validating element [lr].[Sample]: View: [lr].[Sample] has an unresolved reference to unnamed object. Error SQL71501: Error validating element [lr].[Sample]: View: [lr].[Sample] has an unresolved reference to unnamed object.

An example of a failing view definition in SQLProj:

CREATE VIEW [lr].[Sample]
AS
--
-- $Id: StressExtract.sql 114559 2015-11-11 18:31:50Z sharsl $
-- $URL: https://svc-vcs-prd:18080/svn/apps/RCLMReporting/branches/TransformationCash/Src/SQL/Cil/lr/Views/StressExtract.sql $
--
-- ====================================================================
--
-- Copyright (c) 2000-2009 by Mizuho International plc.
--  All Rights Reserved.
--
-- ====================================================================
--
-- Purpose: View to display StressExtract view .
--
-- Author:  Regulatory ISD
-- 
--
--
WITH TripartyPrestartOverrides AS
(
    SELECT * 
    FROM
    (   VALUES
         ('LiquidAssetBuffer','NonUKGovt','AAA')
    ) AS Overrides ([Col1],[Col2],[Col3])
)
SELECT   *
FROM    [lr].[DataExtract] D
LEFT OUTER JOIN TripartyPrestartOverrides O
    ON  D.[LRSecurityClass] = O.[LRSecurityClass]
    AND D.[TransactionStatus] LIKE 'PRESTART%'
    AND D.[ProductType] = 'TRIPARTYREPO'
WHERE    ([ExclusionFlag] = 'N' OR [DataGroup]='Ledger' OR [BookRef]='SCA/OTHER')
GO

Does your dacpac have a reference to the object that is missing? — Ed Elliott, Nov 17 '15 at 18:28
@EdElliott Yes, the reference seems related to the way derived columns in the CTE but there are other examples of failing with different things which don't fail when building in VS2015. — Rubans, Nov 18 '15 at 09:31
@EdElliott We have over 9 dacpacs which relate to a single DB so it's a technical requirement to merge them. However, right now I can't even get it to rebuild a single package in a consistent manner. — Rubans, Nov 18 '15 at 09:33
Thanks, sorry still slightly off topic - why don't you leave the separate and use "This Database" references and IncludeCompositeObjects when deploying to merge them for you? — Ed Elliott, Nov 18 '15 at 10:14
Can you put a demo on github or something? If you create a new TSqlModel it won't have any references or anything - I get round this in Dir2Dac by creating a dummy dacpac with an emptyish model, adding references manually, then opening that dacpac and writing the new model to it (with the references in place): https://github.com/GoEddie/Dir2Dac/blob/master/src/Dir2Dac/DacCreator.cs *but* it would be really helpful if you had a fuller example of what you are doing I can run and see what is wrong. — Ed Elliott, Nov 18 '15 at 10:19
@EdElliott Normally that's what I would so using composite objects works fine when publishing but the problem is that when you have multiple dacpacs and deploy to a single DB, the actual pre deploy which does the evaluation between source dacpac and tartget DB takes a while so when you have multiple dacpacs, this ends up taking a long time so that's one of the main drivers for merging into a single dacpac. You could say why don't we just have a single project in the first place but currently we are trying to avoid this. — Rubans, Nov 18 '15 at 10:29
@EdElliott, I will try and stick a sample project on githhub and get back to you — Rubans, Nov 18 '15 at 10:29
Cool - you might want to look at dir2dac as it basically does the hard work for you, you could extract all your dacpacs / scripts into a folder, use this to create one big one. — Ed Elliott, Nov 18 '15 at 10:52
@EdElliott, I will take a look at your suggestions, sounds like what I need on paper and get back to you — Rubans, Nov 18 '15 at 11:42
@EdElliott, I have uploade dtwo example projects in :https://github.com/rubans/DacMergeExample — Rubans, Nov 24 '15 at 15:14
DacpacMerge is the console app to build the folder and SampleSQLProj is the actual source SQL project — Rubans, Nov 24 '15 at 15:29
@EdElliott, also had a look at Dir2Dacpac but don't think this is for me as I already have the source dacpacs but just need to merge into single dacpac. Let me know if you have any questions. — Rubans, Nov 24 '15 at 15:31
Hey Rubans, I took a look at the sample project and it looks like a bug with the DacFx, if I don't compile the view into the dacpac but add it into the new merged one manually (sampleModel.AddObjects("...)) before doing "DacPackageExtensions.BuildPackage" then it builds just fine so it is just when the view is read from the model and re-written it fails. I have given you a pull request with a sample that does work (by creating a new empty model and adding each script to it) but please raise a microsoft connect bug and link here so it can be fixed properly. — Ed Elliott, Nov 25 '15 at 09:10
@EdElliott Thanks very much for this approach, it works. Annoyed didn't think of it myself :) — Rubans, Dec 09 '15 at 17:19
@EdElliott, thanks for your help with dacpac manipulation. On a diff note, do you know if there's a way to manipulate what data is exported at bacpac level, for e.g. looks like using sqlpackage.exe and export parameter I can specify the tables I would like to include as part of the export but is it possible to go further by limit the dataset by table? Any existing API's out there? Thought I would ask — Rubans, Jan 06 '16 at 16:46
Hi @Rubans I think it is only whole tables *but* the files are just bcp files in the bacpac so you could edit them using the packaging api. — Ed Elliott, Jan 06 '16 at 23:22
@vassilvk I don't believe I did at the the time, I used the workaround suggested. — Rubans, Dec 11 '17 at 12:48

score 0 · Answer 1 · answered Aug 05 '20 at 16:08

There is now an option to avoid those false-positives.
If you use the static method LoadFromDacpac (doc) instead of the contructor of TSqlModel, there is a signature with the ModelLoadOption parameter. If you specify true for the property LoadAsScriptBackedModel (doc) of this object, false-positives disapearded

Error SQL71501 when Rebuilding new dacpac from an existing dacpac

1 Answers1