locked
Scripting variables with schema compare via msbuild RRS feed

  • Question

  • Hi,

    I'm trying to automate comparing the dacpacs we're generating from out builds against our production server to monitor drift.

    However, we use scripting variables to define cross database references.  The schema compare is showing up all the objects which reference the other database via scripting variable as being different to what is on the server i.e. it reports a change between a table referenced as  [$(db)].dbo.Table in the dacpac  and db.dbo.Table in the target database.

    When I do a comparison in Visual studio between the project and the target database the variables seem to be appropriately replaced and the differences don't show.  Obviously this is using a project instead of a dacpac but I'm hoping I can get the dacpac/db compare to behave similarly to the project/db comparison.

    Is there a way to define what the scripting variables should resolve to when I run the comparison via msbuild?

    Thanks,

    George

    Edit:  I would prefer not to deploy the dacpac and diff the deployed db against the target database but if that's the only way....

    • Edited by HerpieG Friday, June 12, 2015 3:16 PM
    Friday, June 12, 2015 11:46 AM

Answers

  • In the end we just set up a dev instance to host the shell databases.

    Not ideal but it works

    • Marked as answer by HerpieG Monday, August 10, 2015 7:46 PM
    Monday, August 10, 2015 7:46 PM

All replies

  • George,

    Is there a specific need for you to compare the dacpacs against production to achieve drift detection? Or would it be a viable option to simply monitor changes as they are applied to your production database? 

    DLM Dashboard is a free tool that lets you achieve this. If it doesn't meet your needs, we'd be keen to hear from you.

    Best regards,

    David

    Redgate


    Product Manager Red Gate Software

    Wednesday, July 22, 2015 1:05 PM
  • See http://stackoverflow.com/questions/24154376/ssdt-msbuild-parameter-overriding-default-value 

    "I looked into this and currently the only supported way to load SQLCMD variables during publish is to save them in the publish profile file (.publish.xml)"

    so you could do that.

    **OR** I generally find that instead of using the msbuild task using sqlpackage.exe does what I want, you can pass in variables and do a compare etc and is generally a much easier way to use SSDT.

    I use msbuild Exec tasks to call sqlpackage.exe and use "/Variables:{PropertyName}={Value}" to pass in variables.

    Hope it helps,

    ed

    Wednesday, July 22, 2015 2:41 PM
  • Hi,

    Thanks for your reply.

    I'm actually trying to ensure that changes that are made to production are also applied to our source control environment.  The output of an SSDT project is a dacpac that I can generate from the code in svn at each checkin via teamcity.

    Then I have a teamcity build that generates a pseudo unit test for each object in the database and fails that test if there is any drift between what is in source control and what is on the server.  Developers can mute tests if they have changes in source control that are not yet in production but this should be short lived on our release branch.

    That's my use case.

    Thanks,

    George

    Monday, August 10, 2015 7:39 PM
  • Hi,

    Thanks, I use this approach to pass in SQLCMD Variables.

    The issue is more that when you run a schema compare using objects with scripting variables they not are replaced during compare time only at deploy time.  So you get lots of false positive differences just because the scripting variables haven't been expanded.

    It seems to figure it out in Visual Studio but not in msbuild compare.

    Monday, August 10, 2015 7:45 PM
  • In the end we just set up a dev instance to host the shell databases.

    Not ideal but it works

    • Marked as answer by HerpieG Monday, August 10, 2015 7:46 PM
    Monday, August 10, 2015 7:46 PM