Invalid file names when trying to deploy SSDT project with TeamCity 8

3
votes

I am trying to deploy Visual Studio 2012 SSDT project to Sql Server using TeamCity 8 and MSBuild Publish task but the deployment fails.

When I look at TeamCity logs and use /v:diag switch in my build configuration I see that for unknown reason MSBuild searches for MyProject.sqlproj.publish.sql and for MyProject.sqlproj.dacpac files.

The exact error:

[SqlPublishTask] C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets(1233, 5): File "C:\Program Files\TeamCity\buildAgent\work\abf8bc05a2cfe7f\*MyProject*\bin\Debug\*MyProject*.sqlproj.dacpac" does not exist.

The correct .sql and .dacpac files get generated (without the .sqlproj in the middel) in buildAgent/work/identificator/*MySolution*/MyProject/bin/Debug folder.

My TeamCity build step is configured as follows:

  • Runner type: MSbuild
  • Build file path: MyProject/*MyProject*.sqlproj
  • MSBuild version: 4.5
  • MsBuild ToolsVersion: 4.0
  • Run platform: 4.0
  • Targets: Publish
  • Command line parameters: /p:SqlPublishProfilePath="Debug.publish.xml" /p:Configuration=Debug

If I execute this from commandline I get no errors.

Any ideas on how can I configure TeamCity to search for correct files or configure my project to generate the files that TeamCity is searching for.

Or is my plan to use MSBuild's Publish task futile and I should utilise sqlpackage.exe instead?

UPDATE

After spending almost three days trying to figure this out I gave up and used sqlpackage.exe which works like a charm. But I would still be interested in an answer though, passing paths to executables in build servers seems a bit crude way to accomplish things.

2
deploymentmsbuildteamcitysql-server-data-tools
I know that I decided to use SQLPackage because it was a bit easier, but have not actually tried to publish with msbuild since we stopped using the DBProj files in favor of SQLProj. You say you get no errors when running your command from the command line, but does it actually do anything to publish the changes? If so, I think you may have an issue in the file path or permissions. Perhaps building and publishing in separate steps could help? – Peter Schott
Already doing it in separate steps. I have checekd permissons on working folder and in the database - everything works. – juhan_h
What happens if you switch that msbuild to publish to a SQLPackage to publish option? – Peter Schott
Thanks for thinking with me Peter, SqlPackage worked the first time so I will be using it until this gets figured out. – juhan_h
We have a related issue. In our case we are publishing fine with sqlpackage.exe. When we BUILD with TeamCity via MSBuild, it outputs files named like MyProject.sqlproj.dacpac. The screwy thing is that, if this file is left sitting on the build server and another build is run, it seems to save MyProject.dacpac as we anticipate. Seems like it might pick up the prior MyProject.sqlproj.dacpac and simply rename it to MyProj.dacpac before producing a new MyProject.sqlproj.dacpac. Did you ever get to the bottom of this? – Michael12345

2 Answers

2
votes

I had a similar issue and came to the conclusion that the way TeamCity produces "pseudo-project" files with *.teamcity suffixes is confusing something in the MSBuild/SSDT target chain.

I simply replaced the MSBuild runner build step with a pure Command Line step and the problem went away.

We lose the user friendliness of the TeamCity MSBuild runner configuration, but if it works, it's a compromise I'm willing to make.

Note - we are running TeamCity 7 - I am not sure if this has been addressed in later versions.

0
votes

I found out you can set a System Property named "system.SqlTargetName" on the build configuration to override the default value.

Setting this to your project name without the ".sqlproj" makes the error go away.