using SqlCmd variables with dbDacFx provider of MsDeploy

Question

I am trying to use msdeploy using dbDacFx provider to deploy my dacpac file. This database project uses a variable named UserName to create logins and users etc. Value for this needs to be passed during deployment time.

So i am doing something like ...

msdeploy.exe -verb:Sync -Source:dbDacFx="c:\dev\pmwuiservices\trunk\Build\database\Database.dacpac"-dest:dbDacFx="Data Source=localhost;Integrated Security=True;Database=UIConfig", -setParam:Kind=SqlCommandVariable,scope=dbDacFx,match=UserName,value="IIS AppPool\ASP.NET V4.0"

but i get the following ( and by the way the url in the error message does not exist)

Info: Adding MSDeploy.dbDacFx (MSDeploy.dbDacFx).
Info: Adding database (data source=localhost;integrated security=True;database=U
IConfig)
Info: Creating deployment plan: Pending.
Info: Verifying deployment plan: Pending.
Info: Deploying package to database: Pending.
Info: Creating deployment plan: Running.
Info: Initializing deployment (Start)
Info: Initializing deployment (Failed)
Info: Creating deployment plan: Faulted.
Info: Verifying deployment plan: Faulted.
Info: Deploying package to database: Faulted.
Error Code: ERROR_EXECUTING_METHOD
More Information: Could not deploy package.
Missing values for the following SqlCmd variables:UserName.
  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_EXECUTING_M
ETHOD.
Error: Could not deploy package.
Error: Missing values for the following SqlCmd variables:UserName.
Error count: 1.

how can one user SqlCmd variables with DbDacFx provider in MSDeploy ?

thanks

JMiera · Accepted Answer · 2013-05-29 00:42:37Z

1

I will confirm that I get the same error. Ahmed Elsayed's article http://www.iis.net/learn/publish/using-web-deploy/dbdacfx-provider-for-incremental-database-publishing, aside from having egregious typos, doesn't address sqlcmd variables either. I personally couldn't find the "real" reference like one would normally expect, such as the one for dbSqlPackage. Furthermore, I found unexplained anecdotal and allegorical variation between "-setParam:kind" and "-setParam:type". Again without documentation to back it up and without having an actual working solution, it does appear that scope can be a filename (regular expression). That said, I've also seen partial snippets by which the authors are defining scope as the provider to which one assigns the parameter. Richard's stuff has worked for me in the past, so am I comfortable moving forward with his suggestion.

edited May 29, 2013 at 0:42

answered May 28, 2013 at 21:41

JMiera

112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Richard Szalay · Accepted Answer · 2012-10-26 22:50:01Z

0

The scope of a SqlCommandVariable parameter should be the filename you are applying it to, not the provider:

msdeploy.exe -verb:sync ^
    -Source:dbDacFx="c:\dev\pmwuiservices\trunk\Build\database\Database.dacpac"
    -dest:dbDacFx="Data Source=localhost;Integrated Security=True;Database=UIConfig" 
    -setParam:kind=SqlCommandVariable,scope=Database.dacpac$,match=UserName,value="IIS AppPool\ASP.NET V4.0"

answered Oct 26, 2012 at 22:50

Richard Szalay

85.1k19 gold badges186 silver badges242 bronze badges

2 Comments

np-hard Over a year ago

i tried your suggestion but i get following Error Code: ERROR_EXECUTING_METHOD More Information: Could not deploy package. Missing values for the following SqlCmd variables:UserName. Learn more at: go.microsoft.com/fwlink/?LinkId=221672#ERROR_EXECUTING_M ETHOD. Error: Could not deploy package. Error: Missing values for the following SqlCmd variables:UserName. Error count: 1.

misterManager Over a year ago

I'm currently having the same issue after attempting the fix mentioned above... have you been able to find a resolution?

Collectives™ on Stack Overflow

using SqlCmd variables with dbDacFx provider of MsDeploy

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related