Can Sql Package Exe Run Post Deployment Script In Command Line?

Question

The following will deploy Sql database with sqlpackage.exe. This does not require use of Publish Profile Xml. Is there a way to add simple post-deployment script inline, without having external post-deployment file?

Eg: will publish database inline, without a publish profile xml.

SqlPackage.exe 
/Action:Publish 
/SourceFile:TestDatabase.dacpac
/TargetDatabaseName:TestDb
/TargetServerName:localhost

Now, goal to add post deployment script in command line, eg insert value in sample table.

Intended Goal:

SqlPackage.exe 
/Action:Publish 
/SourceFile:TestDatabase.dacpac
/TargetDatabaseName:TestDb
/TargetServerName:localhost
/PostDeploymentScript:"insert into dbo.SampleTable (SampleColumn) values ('1')"

Cannot find Post Deployment script inline option in Microsoft website. Maybe it doesn't exist.

https://learn.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-2017

SQLPackage Post Deployment Script not running

Update (Steve Ford answer below may not work):

Want to conduct this from command prompt without any External files. Steve answer may not work for my situation/question.

Why can't you create normal post script and store it as a file? If that's not an option then why not just simply run this insert statement using sqlcmd straight after sqlpackage finished? — Dmitrij Kultasev, Aug 21 '19 at 09:52
I think that you might can do something with deployment contributors but I've never heard about such native functionality — Dmitrij Kultasev, Aug 21 '19 at 14:08
Could you please explain why using SQLPackage may not work. As I stated in my answer the only file you need is the dacpac file. There are no other files apart from the DACPAC the post-deployment script is part of the DACPAC. — Steve Ford, Aug 23 '19 at 13:32
You can use `SQLCMD` to rn an arbitrary script. But what are you trying to do and what are your constraints/requirements? — Nick.Mc, Apr 19 '22 at 12:24

score 1 · Answer 1 · answered Aug 19 '19 at 08:19

1

The way to add a post deployment script requires you to add it to the project.

See Microsoft documentation here: MSDN Pre & post deployment scripts

To add and modify a pre- or post-deployment script use Solution Explorer, expand your database project to display the Scripts folder.

Right click on the Scripts folder and select Add.

Select Scripts in the context menu.

Select Pre-Deployment Script or Post-Deployment Script. Optionally, specify a non-default name. Click Add to finish.

Double click the file in the Scripts folder.

The Transact-SQL editor opens, displaying the contents of the file.

You can use SQLCMD syntax and variables in your scripts and set these in the database project properties. For example:

You can use SQLCMD syntax to include the content of a file in a pre- or post-deployment script. Files are included and run in the order you define them: :r .\myfile.sql

You can use SQLCMD syntax to reference a variable in the post-deployment script. You set the SQLCMD variable in the project properties or in a publish profile:

:setvar TableName MyTable  
insert into [$(TableName)] (SampleColumn) values ('1')

answered Aug 19 '19 at 08:19

Steve Ford

7,433
19
40

1

I want to conduct this from command prompt without any External files – Aug 19 '19 at 15:58
@MattSmith there are no external files, they are included in your DacPac see https://www.mssqltips.com/sqlservertutorial/3006/working-with-pre-and-post-deployment-scripts/ and https://www.c-sharpcorner.com/article/pre-deployment-and-post-deployment-script-sql-database-project/ – Steve Ford Aug 20 '19 at 08:39
hi @SteveFord, I know this can be done with the database project post deployment script, however, this needs to be done conducted through command Line, everything , such as this SqlPackage.exe /Action:Publish /SourceFile:TestDatabase.dacpac /TargetDatabaseName:TestDb /TargetServerName:localhost /PostDeploymentScript:"insert into dbo.SampleTable (SampleColumn) values ('1') – Aug 20 '19 at 14:34
1

@SteveFord has a correct solution to your question. sqlpackage will not execute any additional scripts from command line. If you don't want to execute same script multiple times from sqlpackage add an if stmt or use a merge stmt in your dml script, You should also try explaining why having a dml script in you project is not an acceptable solution. if you want to add additional script to run from command line you can use sqlcmd or switch to deployment using dbup or similar tool. – Daniel N Aug 20 '19 at 14:43
This will perform this through the command line. SQLPackage generates a script which it executes to make the changes, any pre or post deployment script you add to your project will be included in this generated script and executed as one i.e. SQLPackage will execute it, you will not need to do anything other than: SqlPackage.exe /Action:Publish /SourceFile:TestDatabase.dacpac /TargetDatabaseName:TestDb /TargetServerName:localhost – Steve Ford Aug 21 '19 at 10:38

score 0 · Answer 2 · answered Aug 22 '19 at 10:42

When you are asking for specific answer it is usually good idea to explain what exactly do you want to achieve and why. You might stick for specific solution to solve 1 problem that can be solved in many different ways.

What's the real problem you are trying to solve. If the problem is with the fact that post/pre scripts are always executed then you might want to write idempotent script or use some functionality that would execute that script just once. You can check my answer here for that.

If you have other reasons of doing that then you can also achieve that by calling sqlcmd utility or any other one that can execute the statement from the file and run this utility straight away after the sqlpackage.

Now answering your initial question. This is what you need to do if you want to do that with just creating new variable, let's say ReplaceVariable and add single line to the post script:

$(ReplaceVariable)

and then run

sqlpackage.exe 
   /Action:Publish        
   /SourceFile:"Database1.dacpac" 
   /TargetDatabaseName:TestDb 
   /TargetServerName:localhost 
   /Variables:ReplaceVariable="insert into a values (1);"

and if nothing is supposed to be executed, just pass something like "--" as an argument.

ok, thanks, I will still need a post script, but there probably isn't a solution, just a command line type person, ok thanks anyway — , Aug 23 '19 at 08:07

Can Sql Package Exe Run Post Deployment Script In Command Line?

2 Answers2