CODEWITHSUNDEEP
.netsql-serversql-server-2017sqlpackage
user11915641
user11915641

Reputation:

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

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.

Upvotes: 4

Views: 3343

Answers (2)

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

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.

Upvotes: 0

Steve Ford
Steve Ford

Reputation: 7753

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')

Upvotes: 1

Related Questions