Data tier applications - Post Deployment

Question

This is such a simple thing that even asking here is making me feel stupid but since I have been stuck on this for long time, I will ask it here. I am working on a data-tier application in visual studio. I have usual things like tables, stored procs and some post deployment data. By default, data tier application comes with Scripts/Post-Deployment folder. Inside this folder there is a file called Script.PostDeployment.sql. Just to be little more organised, I am creating folders inside Post-Deployment as StaticData and TestData. My insert statements for data creating are locatied inside these folders. So, based on this structure, I am adding following code to my Script.PostDeployment.sql:

    /*
Post-Deployment Script Template                         
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.       
 Use SQLCMD syntax to include a file in the post-deployment script.         
 Example:      :r .\myfile.sql                              
 Use SQLCMD syntax to reference a variable in the post-deployment script.       
 Example:      :setvar TableName MyTable                            
               SELECT * FROM [$(TableName)]                 
--------------------------------------------------------------------------------------
*/

:r .\StaticData\States.sql
:r .\TestData\Logins.sql

The problem is the above code does not work. For some starnge reason, the deploy command just ignores the paths and looks for States.sql and Logins.sql in Scripts/Post-Deployment and not in appropriate subfolders. Anyone else encountered anything similar? Very simple issue, but taking me forever to get around this. I have tried my best to explain, but ask questions and I can try to make things clearer. Thanks!

Just a few points for clarification: (1) what version of Visual Studio are you using? (2) When you say the deploy command is not working, you mean the Deploy command on the Build menu in Visual Studio? (3) How are you determining that it is ignoring the paths - are you seeing something in the Output window that tells you that, are you looking at the files it's built, or something else? — Matthew Burr, Feb 27 '11 at 23:11
(1) Visual Studio 2010 Premium, along with SQL server 2008 R2 is what I am using. (2) Yes the Deploy command from the build menu. (3) The way I know that visual studio is ignoring the paths is when I look in the output window it shows - Error : Could not find file '$\Scripts\Post-Deployment\States.sql'. — AR_, Feb 28 '11 at 01:19
Hmm. I don't experience that. It works like a charm for me, but I do not have SQL 2008 R2 installed on this machine; just SQL Express and VS2010. Let's make sure that it is the lines from your Post-deployment script that are causing the errors. If you remove those lines, does the error go away? Alternatively, if you copy your States.sql and Logins.sql files into the Post-deployment folder, do the errors go away? — Matthew Burr, Feb 28 '11 at 06:39
@matthew-burr Yes the errors go away when I copy the files in Post-deployment folder and leaving the Script.PostDeployment.sql as above. The errors also disappear if I leave the file empty. In the second case, I only get the schema without any post deployment data. Thanks for your help! — AR_, Feb 28 '11 at 14:57
Very odd. I can't reproduce this. I have a hunch the problem is that during deployment it can't access the paths that you specified, and it's falling back to looking in the current directory. If I put a bad path into the post-deployment script, though, I get a different message from what you get. (1) Do you get an error if you just Build the project without deploying it? (2) What "Build Action" and "Copy Output to Directory" settings do you have configured for your post-deployment script? — Matthew Burr, Feb 28 '11 at 15:37
So, to make things bit clearer, I have added the code for sample project [here](http://dl.dropbox.com/u/3965173/SampleDatabase.7z). When I execute build, I get the error: Could not find file '$\Scripts\Post-Deployment\NamesData.sql'. But the problem is compiler is not looking for the file in right place as specified in SCRIPT.POSTDEPLOYMENT.SQL. It may be the case that I am doing some really stupid mistake here but I still have not been able to resolve this. Thanks again @matthew-burr for looking into this. — AR_, Mar 01 '11 at 04:53

score 6 · Accepted Answer · answered Mar 01 '11 at 08:07

I took a look at your sample code. When I had tried to reproduce this, I was using a SQL 2008 database project in Visual Studio 2010, but what your project is a data-tier application, and that is very different; when I switched to using a data-tier application, I was able to reproduce what you're seeing.

Data-tier applications produce DAC packages that contain the definitions of objects and also contain user-defined scripts, like the pre and post deployment scripts. Now, I'm not 100% certain (I haven't used DAC packages before, so I'm basing this on observation and research), but I'm guessing that the file structure of the DAC package doesn't support sub-folders under the Script\Post-deployment folder; I am assuming it has a pretty strict folder structure internally. Consequently, the DACCompiler appears designed to strip out just the filenames from your file references in the post-deployment script, and it ignores the directory path.

There is a whitepaper on data-tier applications here. In it is a section on adding a post-deployment script to the package, and in that section are some best practices, including the following:

• When you work in Solution Explorer, it is recommended that you include all post-deployment commands in the Script.PostDeployment.sql script file. This is because only one post-deployment file is included in the DAC package. In other words, you should not create multiple files.

Now, technically, that's what the :r command does, but you may find it easier to just embed the commands directly into the file manually.

It's also possible that this is simply a bug in the design of the DACCompiler.

Here's what I recommend that you do:

For now, the easiest thing to do - I believe - is just to move the scripts up directly under the Post-Deployment folder; give them unique, descriptive names to compensate for not having the subdirectories.
Alternatively, if you really want to keep the subdirectories, add a pre-build command to your project; have it copy the scripts from the subdirectories into the post-deployment directory before the build starts (you'll need to ensure the scripts have unique filenames)
If you feel that this is a bug, or a feature that should exist, go to
http://connect.microsoft.com/SQLServer and recommend that the product team
address it in a future version of the product. This is a great place to make these kinds of recommendations, because the feedback goes to the product team, the user community at large can vote on feedback to increase its weight, and the product team can communicate back to you with information about the feedback.

And, of course, you could hold out and see if somebody else has a different answer, and if there is, great! But I'm guessing if nobody else has responded yet, then probably there isn't one; I certainly couldn't find anything in my digging.

I hope overall this information is helpful. I wish I could give you a way to have it work now, but I think your best bet is to work within the limitations of the current design and post feedback to Connect.

Good luck.

@matthew-burr Thanks for looking into this. – AR_ Mar 01 '11 at 14:32 — AR_, Mar 01 '11 at 14:32

score 0 · Answer 2 · answered Jan 22 '13 at 08:04

I have a feeling that this will be too late in the pipeline to help with your problem but it might be worth a look. The dacpac format is just a zip file that contains a series of xml files and SQL scripts. If you change the extension of the file to zip then you will be able to access the files that it contains. The postdeploy.sql file should contain the aggregation of your post deployment script and any others that it references.

Jonathan Lurie · Answer 3 · 2016-04-01T21:28:27.217

I just tried this using Visual Studio 2013 and it works.

    IF ( '$(DeployType)' = 'Qualification' ) 
    BEGIN --Run scripts 
        PRINT 'Deploying Qualification Specific scripts.'     
        :r .\Qualification\"QualificationSpecificTestScript.sql"
    END 
    ELSE IF ( '$(DeployType)' = 'Production' ) 
    BEGIN --Run scripts 
        PRINT 'Deploying Production Specific scripts.' 
        :r .\Production\"ProductionSpecificTestScript.sql"
    END

The contents QualificationSpecificTestScript.sql and ProductionSpecificScript.sql are inserted into the generated Post Deployment script.

Here is the generated script file (just the relevant section):

     IF ( '$(DeployType)' = 'Qualification' ) 
     BEGIN --Run scripts 
        PRINT 'Deploying Qualification Specific scripts.'     
            begin transaction;
            PRINT 'IN QUALIFICATION ENVIRONMENT POST DEPLOYMENT SCRIPT'
            commit transaction;
     END
     ELSE IF ( '$(DeployType)' = 'Production' ) 
     BEGIN --Run scripts 
        PRINT 'Deploying Production Specific scripts.' 
            begin transaction;
            PRINT 'IN PRODUCTION ENVIRONMENT POST DEPLOYMENT SCRIPT'
            -- TODO:  Confirm this record should be deleted
            --DELETE TB_VariableName where Id = 9514
            commit transaction;
     END

Data tier applications - Post Deployment

3 Answers3