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.

score 0 · Answer 1 · edited May 23 '17 at 12:16

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

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. — Sercan Aktas, Aug 25 '16 at 17:35
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. — Anton Krouglov, Aug 25 '16 at 17:50

score 0 · Accepted Answer · answered Aug 25 '16 at 20:22

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

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. — Sercan Aktas, Aug 29 '16 at 17:43

Executing dynamic command in PowerShell

2 Answers2