2

Following is the exact scenario in my database project, where I am creating dacpac to deploy databases on target SQL instance:

  • the database project is having a reference of CLR assembly.
  • TFS build server is used to build the project
  • dacpac is getting created during TFS build process

– when i try to run sqlpackage.exe to deploy the dacpac (generated in TFS build process) in my environment, I get an error saying –

*** No file was supplied for reference XXXX.XXXX.XXXX.dll; deployment might fail. When C:\Temp\Dacpac_testing\XXX.XXXXX.XXXX.dacpac was created, the original referenced file was located C:\BUILDS\1\XXXX\XXXX\SRC\XXXXXXXX\ASSEMBLIES\XXXX.XXXX.XXXX.dll

How can I create a dacpac to reference the path of CLR assembly on deployment environment (and not the one that was used at the time of building dacpac)

Any help on this will be much appreciated.

Nirman
  • 6,715
  • 19
  • 72
  • 139
  • @ed-elliott This question http://stackoverflow.com/questions/33179236/how-do-i-reach-out-to-a-dacpac-project-references-through-dac-api has a link to this project which may help: https://github.com/GoEddie/Dacpac-References – Davos Nov 17 '15 at 01:26
  • @kevin-cunnane Just drawing your attention to this if you have any suggestions. In the interests of not duplicating a question I am hijacking this one – Davos Nov 17 '15 at 01:28

2 Answers2

1

Sorry I only picked on this thread now, but I had a similar issue. Open the references in the Sql Server project, go to the dll that you are referencing right click and select properties.

Change the following settings: Generate Sql Script: True Model Aware: True

Select the relevant Permission set as required by the clr code.

Louis Lewis
  • 1,298
  • 10
  • 25
  • 1
    I don't think this answers the question. The OP's issue is that inside the dacpac, there is a path to a CLR DLL (C:\BUILDS...). The dacpac is being deployed from the Build server, not the development machine, therefore the path to the referenced DLL doesn't match. Those other properties won't change the path. Other than creating the exact same directory structure and moving the DLL file there on the build server, or modifying the dacpac to reflect the new path to the DLL, there's not many options – Davos Nov 17 '15 at 01:23
1

I was able to solve this issue by including all the files in the bin/output folder that is built by the SSDT project on the machine that is deploying the dacpac. This folder includes all the referenced DLLs and DAPACs. I also included the publish.xml file and some sample SQLPackage cmd files in the build output to make it easy to deploy.

I found the answer from a post by Richard Gavel in the SQL Server Data Tools forum. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/aba60b70-396f-4624-890c-015c1f2d68f7/dacpac-dependencies-for-sqlpackageexe?forum=ssdt

I don't know if this helps but I was having trouble running SqlPackage /Action:Script between my compiled DACPAC and an extracted DACPAC and got a similar error (An error occurred while adding references). I resolved it by copying all the DACPACS from the \bin\debug directory of my DACPAC (it has about references to 15 other database projects plus master and msdb). After that, it worked. So it seems that in looking for references, it will check the directory first (I don't know if it was looking specifically at the current directory or the one the root DACPAC was in because those were one and the same).

Kevin
  • 333
  • 2
  • 14
  • It a real shame for SSDT and SqlPackage.exe. DacPac always contains a serializations for .NET assemblies in the model.sql `FROM 0x4D5A90000300000004000000FFFF0000B80000000000000040...`. I hope your answer will help. It is so silly. By the way, it's better than nothing. – it3xl Mar 08 '17 at 09:36
  • The `model.xml` also contains assemblies serialization. – it3xl Mar 08 '17 at 09:43
  • Now we have a doubled serialization in `model.sql` and `model.xml` and also punished to copy .dll files too. It is awesome for build-server's artifacts store. Where to participate to an SSDT open source?)) – it3xl Mar 08 '17 at 09:50
  • Oh, I was panicking. There really is a subtle problem. I'm writing one more answer. Shame on me) – it3xl Mar 08 '17 at 21:08
  • It is just a warning. Assemblies and its DB objects will be updated or installed correctly. This was tested. But I still can't suppress this message `*** No file was supplied for reference`. I quit)) Thanks Kevin. – it3xl Mar 09 '17 at 19:31