Using MSBuild to publish a VS 2012 SSDT .sqlproj database project the same way as a VS 2010 .dbproj database project (using command line arguments to specify the database to publish to)
We recently upgraded from VS (Visual Studio) 2010 to VS 2012, and with it had to upgrade our .dbproj database project to a .sqlproj. When making the switch I realized that .sqlproj database projects do not support specifying the database to deploy to as MSBuild command line arguments; instead you have to pass in the path to an xml file that has the necessary information.
So with the old .dbproj database project, you could deploy it to a database using:
MSBuild /t:Deploy /p:TargetDatabase="[DbName]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:DeployToDatabase="True" "[PathToBranch]Database\Database.dbproj"
But with the new .sqlproj database project you have to do:
MSBuild /t:Publish /p:SqlPublishProfilePath="myPublishFile.publish.xml" "[PathToBranch]Database\Database.sqlproj"
Where “myPublishFile.publish.xml” contains the database server and name to publish to.
One other minor thing to note is that it is called “deploying” the database with .dbproj, and is called “publishing” the database with .sqlproj; so when I say Deploy or Publish, I mean the same thing.
We use TFS at my organization and while making new builds for our Test environment, we have the build process deploy the database solution to our various Test databases. This would mean that for us I would either need to:
1 - create a new [DbName].publish.xml file for each database, check it into source control, and update the build template to know about the new file, or
2 - update the file contents of our myPublishFile.publish.xml file dynamically during the build to replace the Database Name and Server in the file before publishing to the database (i.e. read in file contents, replace string, write file contents back to file, publish to DB, repeat).
Option 1 means more work every time I want to add a new Test database to publish to. Option 2 is better, but still means having to update my TF Build template and create a new activity to read/write the new contents to the file.
Instead, there is a 3rd option, which is to simply add the code below to the bottom of the .sqlproj file. This will add some new MSBuild targets to the .sqlproj that will allow us to specify the database name and connection string using similar MSBuild command line parameters that we used to deploy the .dbproj project.
The code presented here is based on this post, but the author has closed the comments section on that post and has not replied to my emails about the bugs in his code and example, so I thought I would share my modified and enhanced solution.
<!-- Custom targets and properties added so that we can specify the database to publish to using command line parameters with VS 2012+ .sqlproj projects, like we did with VS 2010 .dbproj projects. This allows us to specify the MSBuild command-line parameters TargetDatabaseName, and TargetConnectionString when Publishing, and PublishToDatabase when Building. I also stumbled across the undocumented parameter, PublishScriptFileName, which can be used to specify the generated sql script file name, just like DeployScriptFileName used to in VS 2010 .dbproj projects. Taken from: https://blog.danskingdom.com/using-msbuild-to-publish-a-vs-2012-ssdt-sqlproj-database-project-the-same-way-as-a-vs-2010-dbproj-database-project/ --> <PropertyGroup Condition="'$(TargetDatabaseName)' != '' Or '$(TargetConnectionString)' != ''"> <PublishToDatabase Condition="'$(PublishToDatabase)' == ''">False</PublishToDatabase> <TargetConnectionStringXml Condition="'$(TargetConnectionString)' != ''"> <TargetConnectionString xdt:Transform="Replace">$(TargetConnectionString)</TargetConnectionString> </TargetConnectionStringXml> <TargetDatabaseXml Condition="'$(TargetDatabaseName)' != ''"> <TargetDatabaseName xdt:Transform="Replace">$(TargetDatabaseName)</TargetDatabaseName> </TargetDatabaseXml> <TransformPublishXml> <Project xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> <PropertyGroup>$(TargetConnectionStringXml)$(TargetDatabaseXml)</PropertyGroup> </Project> </TransformPublishXml> <!-- In order to do a transform, we HAVE to change the SqlPublishProfilePath --> <SqlPublishProfilePath Condition="'$([System.IO.Path]::IsPathRooted($(SqlPublishProfilePath)))' == 'False'">$(MSBuildProjectDirectory)\$(SqlPublishProfilePath)</SqlPublishProfilePath> <TransformOutputFile>$(MSBuildProjectDirectory)\Transformed_$(TargetDatabaseName).publish.xml</TransformOutputFile> <TransformScope>$([System.IO.Path]::GetFullPath($(MSBuildProjectDirectory)))</TransformScope> <TransformStackTraceEnabled Condition="'$(TransformStackTraceEnabled)'==''">False</TransformStackTraceEnabled> </PropertyGroup> <Target Name="AfterBuild" Condition="'$(PublishToDatabase)'=='True'"> <CallTarget Targets="Publish" /> </Target> <UsingTask TaskName="ParameterizeTransformXml" AssemblyFile="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\Web\Microsoft.Web.Publishing.Tasks.dll" /> <!-- If TargetDatabaseName or TargetConnectionString, is passed in then we use the tokenize transform to create a parameterized sql publish file --> <Target Name="BeforePublish" Condition="'$(TargetDatabaseName)' != '' Or '$(TargetConnectionString)' != ''"> <Message Text="TargetDatabaseName = '$(TargetDatabaseName)', TargetConnectionString = '$(TargetConnectionString)', PublishScriptFileName = '$(PublishScriptFileName)', Transformed Sql Publish Profile Path = '$(TransformOutputFile)'" Importance="high" /> <Error Condition="!Exists($(SqlPublishProfilePath))" Text="The SqlPublishProfilePath '$(SqlPublishProfilePath)' does not exist, please specify a valid file using msbuild /p:SqlPublishProfilePath='Path'" /> <ParameterizeTransformXml Source="$(SqlPublishProfilePath)" IsSourceAFile="True" Transform="$(TransformPublishXml)" IsTransformAFile="False" Destination="$(TransformOutputFile)" IsDestinationAFile="True" Scope="$(TransformScope)" StackTrace="$(TransformStackTraceEnabled)" SourceRootPath="$(MSBuildProjectDirectory)" /> <PropertyGroup> <SqlPublishProfilePath>$(TransformOutputFile)</SqlPublishProfilePath> </PropertyGroup> </Target>
You can get the code here to avoid website copy-paste formatting problems.
So after adding this code at the bottom of the .sqlproj file (above the
</Project> tag though), you can now build and publish the database solution from the MSBuild command line using:
MSBuild /t:Build /p:TargetDatabaseName="[DbName]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishToDatabase="True" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"
Here you can see the 3 new parameters that we’ve added being used: TargetDatabaseName, TargetConnectionString, and PublishToDatabase.
When the TargetDatabaseName or TargetConnectionString parameters are provided we generated a new transformed .publish.xml file, which is the same as the provided “Template.publish.xml” file, but with the database and connection string values replaced with the provided values.
The PublishToDatabase parameter allows us to publish to the database immediately after the project is built; without this you would have to first call MSBuild to Build the database project, and then call MSBuild again to Publish it (or perhaps using “/t:Build;Publish” would work, but I didn’t test that).
If you want to simply publish the database project without building first (generally not recommended), you can do:
MSBuild /t:Publish /p:TargetDatabaseName="[DbName]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"
Be careful though, since if you don’t do a Build first, any changes that have been made since the last time the .sqlproj file was built on your machine won’t be published to the database.
Notice that I still have to provide a path to the template publish.xml file to transform, and that the path to this file is relative to the .sqlproj file (in this example the Template.publish.xml and .sqlproj files are in the same directory). You can simply use one of the publish.xml files generated by Visual Studio, and then the TargetDatabaseName and TargetConnectionString xml element values will be replaced with those given in the command line parameters. This allows you to still define any other publish settings as usual in the xml file.
Also notice that the PublishToDatabase parameter is only used when doing a Build, not a Publish; providing it when doing a Publish will not hurt anything though.
While creating my solution, I also accidentally stumbled upon what seems to be an undocumented SSDT parameter, PublishScriptFileName. While the DeployScriptFileName parameter could be used in VS 2010 .dbproj projects to change the name of the generated .sql file, I noticed that changing its value in the .publish.xml file didn’t seem to have any affect at all (so I’m not really sure why Visual Studio puts it in there). I randomly decided to try passing in PublishScriptFileName from the command line, and blamo, it worked! I tried changing the
So now if I wanted to deploy my database project to 3 separate databases, I could do so with the following code to first Build the project, and the Publish it to the 3 databases:
MSBuild /t:Build "[PathToBranch]\Database\Database.sqlproj" MSBuild /t:Publish /p:TargetDatabaseName="[DbName1]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishScriptFileName="[DbName1].sql" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj" MSBuild /t:Publish /p:TargetDatabaseName="[DbName2]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishScriptFileName="[DbName2].sql" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj" MSBuild /t:Publish /p:TargetDatabaseName="[DbName3]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishScriptFileName="[DbName3].sql" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"
You could also instead just call MSBuild using the Build target with the PublishToDatabase parameter (which might actually be the safer bet); whatever you prefer. I have found that once the database project is built once, as long as no changes are made to it then subsequent “builds” of the project only take a second or two since it detects that no changes have been made and skips doing the build.
If you have any questions or feedback, let me know.
Great work thanks.
I did have to remove the tag in order to get it to work. Otherwise I got the error like System.UriFormatException: Invalid URI: The URI is empty.
The > ?xml? < xml tag in the TransformPublishXml property. Comment system removes angle backets.
Strange, the code in my .sqlproj file is exactly as it appears on the website. Maybe when you copied it over it copied the text funny; I find after I copy text from a website and paste it in an editor I often have to go delete the copied double quotes and re-enter a regular double quote in it’s place.
Nice post, we do something slightly different by replacing the database name and connection string using a custom target in our build file, but I like this more.
Thank you, very helpful!
I had the same problem as sr20, after commenting that offending line out (not an issue with copying-pasting) everything works.
Hi, am trying to use the BeforePublish target to avoid publishing my application if it’s in debug mode and the following snippet added to the project file doesn’t seem to work. can anyone help please.
@sr20 @AnonymousCoward @farouk dz
I’ve provided the code to add to the .sqlproj in the form of a .txt file, so you should be able to safely download that file and copy-paste from there, rather than from your browser. The code is taken straight from my .sqlproj file, so it should be valid. Let me know if you still have problems or not.
Hi Daniel. Thanks for detailed information on this topic. However, I’ve managed to get one strange problem after I’ve recreated your solution on my side. If my connection string is specified using IntegratedSecurity=True, then during the build I get “Unable to connect to target server” error, but if I specify user/password in the connection string - it works perfectly fine. Have you seen anything like this? Thanks in advance!
@Galiya Hmmm, strange, we use Integrated Security exclusively at my company and nobody has mentioned the issue you are experiencing. I know for us all of our developers are in a “Development” group that has permissions for the SQL server and it’s databases; is your Integrated Security relying on group or individual permissions (although I don’t think it should matter)? Are you able to connect to the database via Sql Management Studio using Integrated Security?
@deadlydog Yes, I’ve tested it once again right now. It seems quite bizarre, and I can definitely connect to that sql server via SSMS using Integrated Security (and it’s done similarly via a Development group rather that individual permissions). I also thought that it might be related to the TFS build account not having access to DB, but that didn’t work either even after I specified it as a db_owner. Anyway, thanks for your quick reply. P.S. Btw in your txt file which provides a required addition to sqlproj, I still had to remove <?xml version=”1.0”?> next to , otherwise I was getting the same error “System.UriFormatException: Invalid URI: The URI is empty” @sr20 mentioned above (just in case somebody will have the same issue later)
@Galiya Ok, thanks for verifying you were still getting the UriFormatException. I removed that line from my .sqlproj file and confirmed everything still works properly on my PC, so I have removed that line from the blog post code as well, since it seems to be causing problems on some other people’s PCs for some reason. Thanks.
I was wondering if I could deploy my Database project by Building or Re-Building by solution (which contains other projects) from within Visual Studio 2012. I want to build project so that I can run unit tests after the build process triggered the localdb deploy to deploy any changes. Is this even possible? thanks!
@Neles Hi Neles, I just tested this and calling passing the .sln file instead of the .sqlproj file to MsBuild worked just fine for me. For my specific test it built and deployed the database project and then built the class library project afterward. I don’t have any unit tests, but you would just call MsTest after MsBuild finishes.
Thanks but I have seemed to have run into a transform error. Replaced < with but still the project file wont load since its getting an error due to the ‘xdt:Transform’ text. Getting the error - The project file could not be loaded. ‘xdt’ is an undeclared prefix.
Can anyone please tell me how they made the the project file load ?
where is [PathToBranch] ?
@qinqoushui [PathToBranch] is just the local path on your hard drive. For example, “[PathToBranch]Database\Database.dbproj” might actually be “C:\Projects\MySolution\Database\Database.dbproj”. It can be a relative or absolute path.
Getting this error :
“D:\Delieverables\NShore\Database8\Database8.sqlproj” (Build target) (1) -> (BeforePublish target) -> D:\Delieverables\NShore\Database8\Database8.sqlproj(91,5): error MSB4018: The “ParameterizeTransformXml” task failed unexpectedly.\r D:\Delieverables\NShore\Database8\Database8.sqlproj(91,5): error MSB4018: Syste m.UriFormatException: Invalid URI: The URI is empty.\r D:\Delieverables\NShore\Database8\Database8.sqlproj(91,5): error MSB4018: at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind)\r D:\Delieverables\NShore\Database8\Database8.sqlproj(91,5): error MSB4018: at System.Uri..ctor(String uriString)\r D:\Delieverables\NShore\Database8\Database8.sqlproj(91,5): error MSB4018: at Microsoft.Web.Publishing.Tasks.ParameterizeTransformXml.Execute()\r D:\Delieverables\NShore\Database8\Database8.sqlproj(91,5): error MSB4018: at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecuti onHost.Execute()\r D:\Delieverables\NShore\Database8\Database8.sqlproj(91,5): error MSB4018: at Microsoft.Build.BackEnd.TaskBuilder.d__20.MoveNext()
Fantastic !!! Works great
Leave a Comment
Your email address will not be published. Required fields are marked *