locked
Profiles for multiple project when using MSbuild RRS feed

  • Question

  • I have multiple projects within one solution and I'm trying to figure out if it is possible to deploy all of them with MSbuild like this:

    msbuild /t:build MyDBSolution.sln /p:SqlPublishProfilePath=".\MyDBSolution\Publish\Local.publish.xml"

    The only think is puzzling me is how to set different profiles for project as all of them going to have it's own database.

    Thanks!

    Wednesday, October 3, 2012 9:27 PM

Answers

  • There are two ways you could do this

    1. Add Publish_TargetDatabaseName MSBuild property to each project specifying the database name override.  Warning!  This could override the publish for all configurations depending on where you place the MSBuild property in the project file.  It's also quite hidden.

    2.  A safer way is to break out the MSBuild cmd line above into 1 per project:

    msbuild /t:publish MyDBProject1.sqlproj /p:SqlPublishProfilePath=".\MyDBSolution\Publish\Local.publish.xml" /p:TargetDatabaseName="DB1"

    msbuild /t:publish MyDBProject2.sqlproj /p:SqlPublishProfilePath=".\MyDBSolution\Publish\Local.publish.xml" /p:TargetDatabaseName="DB2"

    Note: this is only possible in our latest release which allows you to specify a publish profile and override any value through an MSBuild variable.  See the Microsoft.Data.Tools.Schema.SqlTasks.Targets file and search for SqlPublishTask:

    <Target Name="SqlPublish"
              Inputs="@(SqlPublishInputItem)"
              Outputs="@(SqlPublishOutputItem)"
              DependsOnTargets="$(SqlPublishDependsOn)">

        <SqlPublishTask
          CacheTargetModel="$(CacheTargetModel)"
          ContributorArguments="@(DeploymentContributorArgument)"
          PublishProfile="$(SqlPublishProfilePath)"
          ScriptFile="@(PublishScriptFile)"
          SourceModel="@(SqlTarget)"
          UpdateDatabase="$(UpdateDatabase)"
          ConnectionString="$(Publish_TargetConnectionString)"
          AllowDropBlockingAssemblies="$(Publish_AllowDropBlockingAssemblies)"
          AllowIncompatiblePlatform="$(Publish_AllowIncompatiblePlatform)"
          BackupDatabaseBeforeChanges="$(Publish_BackupDatabaseBeforeChanges)"
          BlockOnPossibleDataLoss="$(Publish_BlockOnPossibleDataLoss)"
          BlockWhenDriftDetected="$(Publish_BlockWhenDriftDetected)"
          CommentOutSetVarDeclarations="$(Publish_CommentOutSetVarDeclarations)"
          CompareUsingTargetCollation="$(Publish_CompareUsingTargetCollation)"
          CreateNewDatabase="$(Publish_CreateNewDatabase)"
          DeployDatabaseInSingleUserMode="$(Publish_DeployDatabaseInSingleUserMode)"
          DisableAndReenableDdlTriggers="$(Publish_DisableAndReenableDdlTriggers)"
          DoNotAlterChangeDataCaptureObjects="$(Publish_DoNotAlterChangeDataCaptureObjects)"
          DoNotAlterReplicatedObjects="$(Publish_DoNotAlterReplicatedObjects)"
          DropConstraintsNotInSource="$(Publish_DropConstraintsNotInSource)"
          DropDmlTriggersNotInSource="$(Publish_DropDmlTriggersNotInSource)"
          DropExtendedPropertiesNotInSource="$(Publish_DropExtendedPropertiesNotInSource)"
          DropIndexesNotInSource="$(Publish_DropIndexesNotInSource)"
          DropObjectsNotInSource="$(Publish_DropObjectsNotInSource)"
          DropPermissionsNotInSource="$(Publish_DropPermissionsNotInSource)"
          DropRoleMembersNotInSource="$(Publish_DropRoleMembersNotInSource)"
          GenerateSmartDefaults="$(Publish_GenerateSmartDefaults)"
          IgnoreAnsiNulls="$(Publish_IgnoreAnsiNulls)"
          IgnoreAuthorizer="$(Publish_IgnoreAuthorizer)"
          IgnoreColumnCollation="$(Publish_IgnoreColumnCollation)"
          IgnoreComments="$(Publish_IgnoreComments)"
          IgnoreCryptographicProviderFilePath="$(Publish_IgnoreCryptographicProviderFilePath)"
          IgnoreDdlTriggerOrder="$(Publish_IgnoreDdlTriggerOrder)"
          IgnoreDdlTriggerState="$(Publish_IgnoreDdlTriggerState)"
          IgnoreDefaultSchema="$(Publish_IgnoreDefaultSchema)"
          IgnoreDmlTriggerOrder="$(Publish_IgnoreDmlTriggerOrder)"
          IgnoreDmlTriggerState="$(Publish_IgnoreDmlTriggerState)"
          IgnoreExtendedProperties="$(Publish_IgnoreExtendedProperties)"
          IgnoreFileAndLogFilePath="$(Publish_IgnoreFileAndLogFilePath)"
          IgnoreFilegroupPlacement="$(Publish_IgnoreFilegroupPlacement)"
          IgnoreFileSize="$(Publish_IgnoreFileSize)"
          IgnoreFillFactor="$(Publish_IgnoreFillFactor)"
          IgnoreFullTextCatalogFilePath="$(Publish_IgnoreFullTextCatalogFilePath)"
          IgnoreIdentitySeed="$(Publish_IgnoreIdentitySeed)"
          IgnoreIncrement="$(Publish_IgnoreIncrement)"
          IgnoreIndexOptions="$(Publish_IgnoreIndexOptions)"
          IgnoreIndexPadding="$(Publish_IgnoreIndexPadding)"
          IgnoreKeywordCasing="$(Publish_IgnoreKeywordCasing)"
          IgnoreLockHintsOnIndexes="$(Publish_IgnoreLockHintsOnIndexes)"
          IgnoreLoginSids="$(Publish_IgnoreLoginSids)"
          IgnoreNotForReplication="$(Publish_IgnoreNotForReplication)"
          IgnoreObjectPlacementOnPartitionScheme="$(Publish_IgnoreObjectPlacementOnPartitionScheme)"
          IgnorePartitionSchemes="$(Publish_IgnorePartitionSchemes)"
          IgnorePermissions="$(Publish_IgnorePermissions)"
          IgnoreQuotedIdentifiers="$(Publish_IgnoreQuotedIdentifiers)"
          IgnoreRoleMembership="$(Publish_IgnoreRoleMembership)"
          IgnoreRouteLifetime="$(Publish_IgnoreRouteLifetime)"
          IgnoreSemicolonBetweenStatements="$(Publish_IgnoreSemicolonBetweenStatements)"
          IgnoreTableOptions="$(Publish_IgnoreTableOptions)"
          IgnoreUserSettingsObjects="$(Publish_IgnoreUserSettingsObjects)"
          IgnoreWhitespace="$(Publish_IgnoreWhitespace)"
          IgnoreWithNocheckOnCheckConstraints="$(Publish_IgnoreWithNocheckOnCheckConstraints)"
          IgnoreWithNocheckOnForeignKeys="$(Publish_IgnoreWithNocheckOnForeignKeys)"
          IncludeCompositeObjects="$(Publish_IncludeCompositeObjects)"
          IncludeTransactionalScripts="$(Publish_IncludeTransactionalScripts)"
          NoAlterStatementsToChangeCLRTypes="$(Publish_NoAlterStatementsToChangeCLRTypes)"
          PopulateFilesOnFileGroups="$(Publish_PopulateFilesOnFileGroups)"
          RegisterDataTierApplication="$(Publish_RegisterDataTierApplication)"
          ScriptDatabaseCollation="$(Publish_ScriptDatabaseCollation)"
          ScriptDatabaseCompatibility="$(Publish_ScriptDatabaseCompatibility)"
          ScriptDatabaseOptions="$(Publish_ScriptDatabaseOptions)"
          ScriptDeployStateChecks="$(Publish_ScriptDeployStateChecks)"
          ScriptFileSize="$(Publish_ScriptFileSize)"
          ScriptNewConstraintValidation="$(Publish_ScriptNewConstraintValidation)"
          ScriptRefreshModule="$(Publish_ScriptRefreshModule)"
          TargetDatabaseName="$(Publish_TargetDatabaseName)"
          TreatVerificationErrorsAsWarnings="$(Publish_TreatVerificationErrorsAsWarnings)"
          UnmodifiableObjectWarnings="$(Publish_UnmodifiableObjectWarnings)"
          VerifyCollationCompatibility="$(Publish_VerifyCollationCompatibility)"
          VerifyDeployment="$(Publish_VerifyDeployment)"
          />

      </Target>

    Wednesday, October 3, 2012 9:58 PM

All replies

  • There are two ways you could do this

    1. Add Publish_TargetDatabaseName MSBuild property to each project specifying the database name override.  Warning!  This could override the publish for all configurations depending on where you place the MSBuild property in the project file.  It's also quite hidden.

    2.  A safer way is to break out the MSBuild cmd line above into 1 per project:

    msbuild /t:publish MyDBProject1.sqlproj /p:SqlPublishProfilePath=".\MyDBSolution\Publish\Local.publish.xml" /p:TargetDatabaseName="DB1"

    msbuild /t:publish MyDBProject2.sqlproj /p:SqlPublishProfilePath=".\MyDBSolution\Publish\Local.publish.xml" /p:TargetDatabaseName="DB2"

    Note: this is only possible in our latest release which allows you to specify a publish profile and override any value through an MSBuild variable.  See the Microsoft.Data.Tools.Schema.SqlTasks.Targets file and search for SqlPublishTask:

    <Target Name="SqlPublish"
              Inputs="@(SqlPublishInputItem)"
              Outputs="@(SqlPublishOutputItem)"
              DependsOnTargets="$(SqlPublishDependsOn)">

        <SqlPublishTask
          CacheTargetModel="$(CacheTargetModel)"
          ContributorArguments="@(DeploymentContributorArgument)"
          PublishProfile="$(SqlPublishProfilePath)"
          ScriptFile="@(PublishScriptFile)"
          SourceModel="@(SqlTarget)"
          UpdateDatabase="$(UpdateDatabase)"
          ConnectionString="$(Publish_TargetConnectionString)"
          AllowDropBlockingAssemblies="$(Publish_AllowDropBlockingAssemblies)"
          AllowIncompatiblePlatform="$(Publish_AllowIncompatiblePlatform)"
          BackupDatabaseBeforeChanges="$(Publish_BackupDatabaseBeforeChanges)"
          BlockOnPossibleDataLoss="$(Publish_BlockOnPossibleDataLoss)"
          BlockWhenDriftDetected="$(Publish_BlockWhenDriftDetected)"
          CommentOutSetVarDeclarations="$(Publish_CommentOutSetVarDeclarations)"
          CompareUsingTargetCollation="$(Publish_CompareUsingTargetCollation)"
          CreateNewDatabase="$(Publish_CreateNewDatabase)"
          DeployDatabaseInSingleUserMode="$(Publish_DeployDatabaseInSingleUserMode)"
          DisableAndReenableDdlTriggers="$(Publish_DisableAndReenableDdlTriggers)"
          DoNotAlterChangeDataCaptureObjects="$(Publish_DoNotAlterChangeDataCaptureObjects)"
          DoNotAlterReplicatedObjects="$(Publish_DoNotAlterReplicatedObjects)"
          DropConstraintsNotInSource="$(Publish_DropConstraintsNotInSource)"
          DropDmlTriggersNotInSource="$(Publish_DropDmlTriggersNotInSource)"
          DropExtendedPropertiesNotInSource="$(Publish_DropExtendedPropertiesNotInSource)"
          DropIndexesNotInSource="$(Publish_DropIndexesNotInSource)"
          DropObjectsNotInSource="$(Publish_DropObjectsNotInSource)"
          DropPermissionsNotInSource="$(Publish_DropPermissionsNotInSource)"
          DropRoleMembersNotInSource="$(Publish_DropRoleMembersNotInSource)"
          GenerateSmartDefaults="$(Publish_GenerateSmartDefaults)"
          IgnoreAnsiNulls="$(Publish_IgnoreAnsiNulls)"
          IgnoreAuthorizer="$(Publish_IgnoreAuthorizer)"
          IgnoreColumnCollation="$(Publish_IgnoreColumnCollation)"
          IgnoreComments="$(Publish_IgnoreComments)"
          IgnoreCryptographicProviderFilePath="$(Publish_IgnoreCryptographicProviderFilePath)"
          IgnoreDdlTriggerOrder="$(Publish_IgnoreDdlTriggerOrder)"
          IgnoreDdlTriggerState="$(Publish_IgnoreDdlTriggerState)"
          IgnoreDefaultSchema="$(Publish_IgnoreDefaultSchema)"
          IgnoreDmlTriggerOrder="$(Publish_IgnoreDmlTriggerOrder)"
          IgnoreDmlTriggerState="$(Publish_IgnoreDmlTriggerState)"
          IgnoreExtendedProperties="$(Publish_IgnoreExtendedProperties)"
          IgnoreFileAndLogFilePath="$(Publish_IgnoreFileAndLogFilePath)"
          IgnoreFilegroupPlacement="$(Publish_IgnoreFilegroupPlacement)"
          IgnoreFileSize="$(Publish_IgnoreFileSize)"
          IgnoreFillFactor="$(Publish_IgnoreFillFactor)"
          IgnoreFullTextCatalogFilePath="$(Publish_IgnoreFullTextCatalogFilePath)"
          IgnoreIdentitySeed="$(Publish_IgnoreIdentitySeed)"
          IgnoreIncrement="$(Publish_IgnoreIncrement)"
          IgnoreIndexOptions="$(Publish_IgnoreIndexOptions)"
          IgnoreIndexPadding="$(Publish_IgnoreIndexPadding)"
          IgnoreKeywordCasing="$(Publish_IgnoreKeywordCasing)"
          IgnoreLockHintsOnIndexes="$(Publish_IgnoreLockHintsOnIndexes)"
          IgnoreLoginSids="$(Publish_IgnoreLoginSids)"
          IgnoreNotForReplication="$(Publish_IgnoreNotForReplication)"
          IgnoreObjectPlacementOnPartitionScheme="$(Publish_IgnoreObjectPlacementOnPartitionScheme)"
          IgnorePartitionSchemes="$(Publish_IgnorePartitionSchemes)"
          IgnorePermissions="$(Publish_IgnorePermissions)"
          IgnoreQuotedIdentifiers="$(Publish_IgnoreQuotedIdentifiers)"
          IgnoreRoleMembership="$(Publish_IgnoreRoleMembership)"
          IgnoreRouteLifetime="$(Publish_IgnoreRouteLifetime)"
          IgnoreSemicolonBetweenStatements="$(Publish_IgnoreSemicolonBetweenStatements)"
          IgnoreTableOptions="$(Publish_IgnoreTableOptions)"
          IgnoreUserSettingsObjects="$(Publish_IgnoreUserSettingsObjects)"
          IgnoreWhitespace="$(Publish_IgnoreWhitespace)"
          IgnoreWithNocheckOnCheckConstraints="$(Publish_IgnoreWithNocheckOnCheckConstraints)"
          IgnoreWithNocheckOnForeignKeys="$(Publish_IgnoreWithNocheckOnForeignKeys)"
          IncludeCompositeObjects="$(Publish_IncludeCompositeObjects)"
          IncludeTransactionalScripts="$(Publish_IncludeTransactionalScripts)"
          NoAlterStatementsToChangeCLRTypes="$(Publish_NoAlterStatementsToChangeCLRTypes)"
          PopulateFilesOnFileGroups="$(Publish_PopulateFilesOnFileGroups)"
          RegisterDataTierApplication="$(Publish_RegisterDataTierApplication)"
          ScriptDatabaseCollation="$(Publish_ScriptDatabaseCollation)"
          ScriptDatabaseCompatibility="$(Publish_ScriptDatabaseCompatibility)"
          ScriptDatabaseOptions="$(Publish_ScriptDatabaseOptions)"
          ScriptDeployStateChecks="$(Publish_ScriptDeployStateChecks)"
          ScriptFileSize="$(Publish_ScriptFileSize)"
          ScriptNewConstraintValidation="$(Publish_ScriptNewConstraintValidation)"
          ScriptRefreshModule="$(Publish_ScriptRefreshModule)"
          TargetDatabaseName="$(Publish_TargetDatabaseName)"
          TreatVerificationErrorsAsWarnings="$(Publish_TreatVerificationErrorsAsWarnings)"
          UnmodifiableObjectWarnings="$(Publish_UnmodifiableObjectWarnings)"
          VerifyCollationCompatibility="$(Publish_VerifyCollationCompatibility)"
          VerifyDeployment="$(Publish_VerifyDeployment)"
          />

      </Target>

    Wednesday, October 3, 2012 9:58 PM
  • Hi Janet,

    that does not work as described in option 2.

    First off, I need to set an additional property "VisiualStudioVersion" to "11.0" when I try to build the projects directly. Otherwise there is an error:

    "C:\w\xyz\components\core_model\src\core_model_tests\core_model_tests.sqlproj" (publish target) (1) ->
      C:\w\xyz\components\core_model\src\core_model_tests\core_model_tests.sqlproj(57,3): error MSB4019: The imported project "C:\Program Files (x86)\MSBuild\Microsoft\VisualStud
    io\v10.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.

    Looking at a project file I found that there is a default pointint to the version 10 of VS. It seems the default is overridden when using the solution file as a build file. But actually that is a question for a diffenrent topic.

    Back to the main topic: Overriding the values in the publish profile.

    Here is my command line:

    msbuild /p:VisualStudioVersion=11.0 /t:build;publish /p:SqlPublishProfilePath=..\..\..\UnitTesting.publish.xml /p:Publish_TargetDatabaseName="rktest2" core_model_tests.sqlproj

    As you can see, I tried overriding the property Publish_TargetDatabaseName as well as just TargetDatabaseName. Neither works. The values in the publish profile are always winning. I initially tried to do that from within a build file with the same results. I even tried to add a

       Condition=" '$(TargetDatabaseName)' == '' "

    to the property in the publish profile to avoid the value there if it is already set. I removed the property from the publish profile, but then I get an ArgumentNullException.

    Kind Regards

    Ralf


    ralf


    • Edited by ralfkret Monday, October 15, 2012 12:51 PM formatting
    Monday, October 15, 2012 12:50 PM