locked
dacpac version RRS feed

  • Question

  • I'm adding a  reference to  a dacpac. Is there a way to see the version of this dacpac from VS or somewhere else? 

    I don't like the idea to add version into the file name because I'll have to add new reference when I have new version. 

    Thanks! 

     

     

    Thursday, January 12, 2012 12:22 AM

Answers

  • Thanks for confirming. We currently do not display these properties, but that would indeed be useful. I will add it to the backlog.

     


    -GertD @ www.sqlproj.com
    • Proposed as answer by Janet Yeilding Monday, January 23, 2012 10:37 PM
    • Marked as answer by Janet Yeilding Tuesday, January 31, 2012 12:15 AM
    Sunday, January 22, 2012 10:11 PM
    Answerer

All replies

  • Currently there is no such thing as a version stamp of the dacpac that is begin exposed, only the information inside the property window

    What kind of version stamp are you looking for? I can imaging we could add the date time stamp in there. Or are you refering to the DAC version number that you entered?


    -GertD @ www.sqlproj.com
    Thursday, January 19, 2012 5:34 PM
    Answerer
  • Yes, I was referring to the DAC version number I entered. 

     

    Friday, January 20, 2012 10:47 PM
  • Thanks for confirming. We currently do not display these properties, but that would indeed be useful. I will add it to the backlog.

     


    -GertD @ www.sqlproj.com
    • Proposed as answer by Janet Yeilding Monday, January 23, 2012 10:37 PM
    • Marked as answer by Janet Yeilding Tuesday, January 31, 2012 12:15 AM
    Sunday, January 22, 2012 10:11 PM
    Answerer
  • Gert,

      Has there been any change in being able to use these version numbers outside of just setting the properties? I could see this being very useful for being able to run Pre/Post Deploy scripts to run scripts that are appropriate for each version. It would also be helpful to easily store/query that value somewhere so we could know which version is in the environment.

    Tuesday, July 17, 2012 2:41 PM
  • Hi Peter, interesting point. This is my concern with the usefulness of this suggestion in topic.

    One solution would be to have the ability for SSDT to persist the version to an extended property of our choice - this would be nice and would solve the other half of this problem that you expose although it would matter when this version property was actually deployed depending on how it could be used.

    These extended properties would be useful if you are referencing external databases and you want pre-requisite checking in pre-deploy to ensure correct version before deploying.

    I have actually completely moved away from using version numbers in the way that you describe in Pre/Post Deploy since SSDT. It was really messy, counter-intuitive (with pre scripts having to know the last version and post-scripts having to know the next version) and I felt generally 1 mistake away from a really bad release day (e.g. oops THAT delete statement shouldn't have run on THIS version!).

    I have instead been using the new Snapshots functionality to capture the state of pre/post and schema for each specific version, naming the dacpac with the version number in it itself and then deploying that dacpac itself through environments for testing/release.

    This way the pre/post scripts are cleaner,  there is never any old version code stuck in later versions (with ever-growing unnecessary code that gives me and my DBA sleepless nights). there is no logic to go wrong to check the current version. You then deploy the snapshot (I use sqlpackage.exe called from a control powershell script) and if it works on a representative pre-production it will work on production.

    I still have a database version extended property as I used to with vs2010 data dude, but actually I don't seem to need to check it anymore as I am only including those pre/post scripts in the current version. Took me a while to be happy with it but it is fantastic. Perhaps I'll do a blog post or something to describe in more detail as I think this really is an important technique that improves on datadude enormously.

    Tuesday, July 17, 2012 4:38 PM
  • Using snapshots is my next idea - a good way of keeping those scripts clean. However, it would be really nice to know which version we've actually released to the server(s). Not being able to access that version number in any way makes me wonder why it's even exposed. If it were written as an extended property or stored in a table, or even if we could reference it as a variable I could see a good use for storing that information somehow so we would then know what version we're about to release.

    I'd love to be able to include that in a folder or filename as well so we could easily generate those snapshots in a manner that corresponds to the version.

    How are you handling the DB Version extended property now? That sounds like something we could use to record the versions.

    I pretty much agree with you about handling things appropriately through snapshots. However, we would definitely get some benefits from knowing that the correct version of the DB Schema has been released with a certain set of code (if only through peace of mind for our release manager).

    Wednesday, July 18, 2012 5:50 PM
  • The version you are talking about is for Data-tier applications which is something that SQL Server provides similar to what SSDT provides out of the box - they are related however they are *not* the same as the dacpac that is produced and used by sqlpackage.exe. I believe that this is what that version property was originally built for. Read about data-tier applications here: http://msdn.microsoft.com/en-us/library/ee210546.aspx

    I put the version number and date produced in my version snapshots (for human/sqlpackage.exe deploy):

    MyProject v01.001 20120719.dacpac

    I have the extended property simply on the database level for checking what has been deployed.

    EXEC sp_addextendedproperty @name='DbVersion', 
    	@value ='1.015', 
    	@level0type = NULL,
    	@level0name = NULL, 
    	@level1type = NULL,
    	@level1name = NULL, 
    	@level2type = NULL,
    	@level2name = NULL
    

    Where a project is designed to be reused inside other projects, I use a name that relates to the package to avoid clashes (E.g. MyTools_DbVersion)

    You can then interrogate extended properties using the standard system sp's, however be aware that you have the problem that in the pre-deploy scripts you can't see the version that you are deploying. But like I say now I've gone to snapshots the only reason I can see I will want to check versions is to check that dependant seperate databases are at the required version before release (pre-requisite check), and so this problem doesn't surface.

    However you look at it, the version property held in the dacpac itself is still just a label and no better than any other method and currently it has the problem that you can't interrogate it in t-sql/cmdsql.

    Getting the dacpac version to auto-generate an extended property however - would cover all angles.

    Thursday, July 19, 2012 8:49 AM
  • I was able to expose the DacVersion to my pre/post install scripts via the following steps.  They use some MSBuild features to publish the DacVersion to a small SQL file, with a SQL Command variable, that is then included in the pre-deployment scripts.

    Add SetDacVersion.targets to SQL project folder

    This auxiliary msbuild file writes a SQL file to :setvar DacVersion to the project file DacVersion.  It is sqlproj-independent, so can be re-used in multiple projects without editing.

     

    <?xml version="1.0" encoding="utf-8"?>

    <Project DefaultTargets="WriteDacVersion" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">

    <Target Name="WriteDacVersion"

    Inputs="$(MSBuildProjectFile)"

    Outputs="Scripts/Pre-Deployment/SetDacVersion.sql"

    >

    <Message Text="Writing SetDacVersion.sql" />

    <WriteLinesToFile

    File="scripts/Pre-Deployment/SetDacVersion.sql"

    Lines="-- Generated by MsBuild Step - DO NOT EDIT%0A:setvar DacVersion &quot;$(DacVersion)&quot;"

    Overwrite="true"

    Encoding="Unicode" />

    </Target>

    </Project>

     

    Add code in .sqlproj to import the WriteDacVersion.targets

    Add the highlighted text and line near the top of the sqlproj.

     

    <Project DefaultTargets="Build;WriteDacVersion" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">

    <Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />

    <Import Project="WriteDacVersion.targets" />

     

    Add SetDacVersion to the SqlProj file

    Run a build.  The SQL file is written to Script/Pre-Deployment/SetDacVersion.sql.  Add Existing Item to add this file to the project.  The file needs to be flagged None for the build state.

     

    Add SetDacVersion to pre-deploy

    Edit your pre-deployment script to include the SetDacVersion.sql (the second line here is debugging/logging output.)

    :r SetDacVersion.sql

    print 'DacVersion = $(DacVersion)';

    Tuesday, June 2, 2015 10:08 AM