Command is running fine in console but not when used by Powershell

Question

I have a PS script that should deploy a project to my SSIS server. When I run the generated command in a console it runs fine but when the command is executed from Powershell it fails because of this (windows) error :

TITLE: SQL Server Integration Services

The path format is invalid. Parameter name: DestinationPath (ISDeploymentWizard)

ADDITIONAL INFORMATION:

The path format is invalid. (Microsoft.SqlServer.IntegrationServices.Wizard.Common)

If I run the generated command from a console it runs fine:

D:\Deploy\ISDeploymentWizard.exe /Silent /ModelType:Project /SourcePath:"D:\Deploy\Receive\My_Beautiful_Project.ispac" /DestinationServer:"localhost" /DestinationPath:"/SSISDB/My Beautiful Project/My_Beautiful_Project" /ProjectPassword:"SuperSecretPassword"

The script (thanks to suggestions from Guenther Schmitz and Janne Tukaanen) :

#region script configuration
$SsisServer = "."
$ProjectFileFolder = "D:\Deploy\Receive"
$ProjectFileName = "My_Beautiful_Project.ispac"
$ProjectFilePassword = "SuperSecretPassword"
$FolderName = "My Beautiful Project"
$ProjectName = "My_Beautiful_Project"
$ISDeploymentWizard = "D:\Deploy\ISDeploymentWizard.exe"
#endregion

#region project deployment
# Create command line arguments
$DestinationPath = "/SSISDB/" + $FolderName + "/" + $ProjectName
$ProjectFilePath = $ProjectFileFolder + "\" + $ProjectFileName
$cmd = $ISDeploymentWizard
$arg1 = "/Silent"
$arg1a= "/ModelType:Project"
$arg2 = "/SourcePath:""$ProjectFilePath"""
$arg3 = "/DestinationServer:""$SsisServer"""
$arg4 = "/DestinationPath:""$DestinationPath"""
$arg5 = "/ProjectPassword:""$ProjectFilePassword"""
Write-Host "$cmd" $arg1 $arg1a $arg2 $arg3 $arg4 $arg5
& "$cmd" $arg1 $arg1a $arg2 $arg3 $arg4 $arg5


Write-Host "Done"
#endregion

Isn't $cmd missing from you execution line? You are trying to run $arg1 now. — Janne Tuukkanen
– Janne Tuukkanen, Commented Nov 29, 2018 at 7:50
It was indeed. But now I have a different error :| (see question) — Henrov
– Henrov, Commented Nov 29, 2018 at 9:14
did you try the pure powershell solution? learn.microsoft.com/en-us/sql/integration-services/… — Guenther Schmitz
– Guenther Schmitz, Commented Dec 1, 2018 at 7:42

Yahfoufi · Accepted Answer · 2018-12-11 10:41:23Z

4

+50

There is no need to declare the following variables $arg1 $arg1a $arg2 $arg3 $arg4 $arg5, just run the following command (why declaring variables and storing their values in another variables??):

& $cmd /Silent /ModelType:Project /SourcePath:$ProjectFilePath /DestinationServer:$SsisServer /DestinationPath:$DestinationPath /ProjectPassword:$ProjectFilePassword

edited Dec 11, 2018 at 10:41

answered Dec 10, 2018 at 15:33

Yahfoufi

2,5502 gold badges26 silver badges44 bronze badges

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

1 Comment

Henrov Over a year ago

This did it! Now the deploy runs.

Guenther Schmitz · Accepted Answer · 2018-11-29 07:58:48Z

2

you are missing the executable in the line below Write-Host.

change

& $arg1 $arg2 $arg3 $arg4 $arg5

to

& $cmd $arg1 $arg2 $arg3 $arg4 $arg5

answered Nov 29, 2018 at 7:58

Guenther Schmitz

2,0191 gold badge13 silver badges23 bronze badges

1 Comment

Henrov Over a year ago

I did that. I also noticed that the string provided when I manually run ISDeploymentWizard has an extra parameter. I added that also. But when I run it now I get a different error (updated question)

Mike Twc · Accepted Answer · 2018-12-15 19:06:35Z

1

If you have troubles to start console apps in powershell (typically because of multiple arguments), you may execute it through cmd (in powershell)

cmd /c "$cmd $arg1 $arg2 $arg3 $arg4 $arg5"

There is also another option using Process class, so you don't have to use cmd:

$ProcessInfo = New-Object System.Diagnostics.ProcessStartInfo 
$ProcessInfo.FileName = "D:\Deploy\ISDeploymentWizard.exe"
$ProcessInfo.Arguments = "$arg1 $arg1a $arg2 $arg3 $arg4 $arg5"
$ProcessInfo.RedirectStandardError = $true 
$ProcessInfo.RedirectStandardOutput = $true 
$ProcessInfo.UseShellExecute = $false 
$Process = New-Object System.Diagnostics.Process 
$Process.StartInfo = $ProcessInfo 

$Process.Start() | Out-Null 
$output = $Process.StandardOutput.ReadToEnd() 
$errors = $Process.StandardError.ReadToEnd()
$Process.WaitForExit() 
Write-Host $output 
Write-Error $errors

You can check this for some more details: PowerShell, stream Process output and errors while running external process

edited Dec 15, 2018 at 19:06

answered Dec 4, 2018 at 17:32

Mike Twc

2,3752 gold badges17 silver badges20 bronze badges

8 Comments

Henrov Over a year ago

Unfortunately, it still fails on the Destinationpath. I tried a destinationpath without spaces but it gives me the same error. So it seems not to be the content of destinationpath that causes the error.

Mike Twc Over a year ago

Strange. Can you just copy a working command from console (without string building). Like cmd /c 'working command here' Also try to use single quotes, sometimes it matters

Mike Twc Over a year ago

Also check this link, it uses slightly diffferent approach for that (for PS) stackoverflow.com/questions/21555086/…

Mike Twc Over a year ago

Try to remove spaces from destination folder (somebody mentioned that might be an issue). I think you can also save your working cmd command as a bat file, and just call it from powershell (by typing full path to that bat)

Mike Twc Over a year ago

Just realized there is another approach for that, updated my answer

|

Nathan Rice · Accepted Answer · 2018-12-08 00:45:19Z

0

Pretty sure that the $DestinationPath needs to be a non relative path. Change it to the full path including the drive and I think that will solve your problem.

answered Dec 8, 2018 at 0:45

Nathan Rice

3,1211 gold badge22 silver badges30 bronze badges

1 Comment

Henrov Over a year ago

DestinationPath is the destinationpath within the SSIS catalog. I do not think there is a drive involved?

Collectives™ on Stack Overflow

Command is running fine in console but not when used by Powershell

4 Answers 4

1 Comment

1 Comment

8 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related