Executing dynamic command in PowerShell

Question

My script is as follows

New-Alias -Name sqlpackage -Value "C:\Program Files (x86)\Microsoft SQL 
Server\130\DAC\bin\sqlpackage.exe"
$command = "sqlpackage /Action:Publish /SourceFile:$sourceFile /tcs:$TargetConnectionString /p:ScriptRefreshModule="+"$ScriptRefreshModule"+" /p:ScriptNewConstraintValidation="+"$ScriptNewConstraintValidation"+ `
" /p:GenerateSmartDefaults="+"$GenerateSmartDefaults"+" /p:BlockOnPossibleDataLoss="+"$BlockOnPossibleDataLoss"+" /p:BackupDatabaseBeforeChanges="+"$BackupDatabaseBeforeChanges"+ `
" /v:ErrorEmailRecipients="+"$ErrorEmailRecipients"+" /v:DeploymentEnvironment="+"$environment"+ $additionalVariables
& $command

This fails with sqlpackage cmdlet not found. I tried putting the entire exe path to command instead of using alias which didnt change the outcome as well.

I also tried Invoke-Command, Invoke-Item and Invoke-Expression, all failed to run as well. I am thinking like bash script which might be my downfall. What should I do to make this command work?

It needs to be dynamic since the variables are changing according to the source files.

Community · Accepted Answer · 2017-05-23 12:16:19Z

0

New-Alias wont work as it defines aliases for existing cmdlets. This function worked for me (as per here and here):

function sqlpackage(
    $sourceFile `
    ,$TargetConnectionString `
    ,$ScriptRefreshModule `
    ,$ScriptNewConstraintValidation `
    ,$GenerateSmartDefaults `
    ,$BlockOnPossibleDataLoss `
    ,$BackupDatabaseBeforeChanges `
    ,$ErrorEmailRecipients `
    ,$environment `
    ,$additionalVariable1 `
    ,$additionalVariable2 `
    ,$additionalVariable3 `
)
    {
    $command = "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe"

    &$command `
        /Action:Publish `
        /SourceFile:$sourceFile `
        /tcs:$TargetConnectionString `
        /p:ScriptRefreshModule=$ScriptRefreshModule `
        /p:ScriptNewConstraintValidation=$ScriptNewConstraintValidation `
        /p:GenerateSmartDefaults=$GenerateSmartDefaults `
        /p:BlockOnPossibleDataLoss=$BlockOnPossibleDataLoss `
        /p:BackupDatabaseBeforeChanges=$BackupDatabaseBeforeChanges `
        /v:ErrorEmailRecipients=$ErrorEmailRecipients `
        /v:DeploymentEnvironment=$environment `
       $additionalVariable1 `
       $additionalVariable2 `
       $additionalVariable3 `
}

sqlpackage `
    $sourceFile `
    $TargetConnectionString `
    $ScriptRefreshModule `
    $ScriptNewConstraintValidation `
    $GenerateSmartDefaults `
    $BlockOnPossibleDataLoss `
    $BackupDatabaseBeforeChanges `
    $ErrorEmailRecipients `
    $environment `
    $additionalVariable1 `
    $additionalVariable2 `
    $additionalVariable3

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Aug 25, 2016 at 16:21

Anton Krouglov

3,4693 gold badges37 silver badges53 bronze badges

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

2 Comments

Sercan Aktas Over a year ago

The tricky part which made me make it a string was the $additionalVariables . It is different for each source. i.e. $additionalVariables = " /v:ClearData="no" in one and $additionalVariables = " /v:Products="Products" in another etc.

Anton Krouglov Over a year ago

Well, if there is a limited amount of possible additional flags (say 3) you still use this calling syntax (without building a string). See updated sample above. If during a particular call there is only one additional flag - set flags 2 and 3 to empty string.

Sercan Aktas · Accepted Answer · 2016-08-25 20:22:30Z

0

I used a part of your answer and moved away from Invoke and & and it worked. Also New-Alias part is working too.

New-Alias -Name sqlpackage -Value "C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\sqlpackage.exe"
sqlpackage `
  /Action:Publish `
  /SourceFile:$sourceFile `
  /tcs:$TargetConnectionString `
  /p:ScriptRefreshModule=$ScriptRefreshModule `
  /p:ScriptNewConstraintValidation=$ScriptNewConstraintValidation `
  /p:GenerateSmartDefaults=$GenerateSmartDefaults `
  /p:BlockOnPossibleDataLoss=$BlockOnPossibleDataLoss `
  /p:BackupDatabaseBeforeChanges=$BackupDatabaseBeforeChanges `
  /v:ErrorEmailRecipients=$ErrorEmailRecipients `
  /v:DeploymentEnvironment=$environment.LifeCycle `
  $additionalVariables

answered Aug 25, 2016 at 20:22

Sercan Aktas

337 bronze badges

1 Comment

Sercan Aktas Over a year ago

Also if you need more than one variable you can do $additionalVariables = "/v:Products="Products /v:ClearData="no" and then $additionalVariables.split(' ') which will get all.

Collectives™ on Stack Overflow

Executing dynamic command in PowerShell

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related