8

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
Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
Henrov
  • 1,610
  • 1
  • 24
  • 52
  • 2
    Isn't `$cmd` missing from you execution line? You are trying to run `$arg1` now. – Janne Tuukkanen Nov 29 '18 at 07:50
  • It was indeed. But now I have a different error :| (see question) – Henrov Nov 29 '18 at 09:14
  • replace all the curly 'smart' quotes with straight ones – Theo Nov 29 '18 at 10:04
  • That is formatting due to stackoverflow :| – Henrov Nov 29 '18 at 10:09
  • 1
    did you try the pure powershell solution? https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-deploy-powershell?view=sql-server-2017 – Guenther Schmitz Dec 01 '18 at 07:42
  • Good Guenther! Thanx! – Henrov Dec 02 '18 at 08:07
  • The thing is: the package is secured with a password (EncryptSensitiveWithPassword). I can't find how to implement that in the powershell solution? – Henrov Dec 02 '18 at 08:13
  • Apparently DeployProject($ProjectName, $projectFile) has no way to set the password. So I need to get this to work... – Henrov Dec 03 '18 at 19:15
  • This is would store your password in a file `(get-credential).password | ConvertFrom-SecureString | set-content 'C:\your_path\password.txt'` – tukan Dec 04 '18 at 09:05
  • To see what has gone wrong just look at a command line of the process (using Windows' Task Manager or Process Explorer) when it shows you the error and compare it with the working command line. – montonero Dec 07 '18 at 08:18

4 Answers4

4

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
Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
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
Guenther Schmitz
  • 1,955
  • 1
  • 9
  • 23
  • 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) – Henrov Nov 29 '18 at 08:46
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

Mike Twc
  • 2,230
  • 2
  • 14
  • 19
  • 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. – Henrov Dec 05 '18 at 20:03
  • 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 Dec 05 '18 at 20:27
  • Also check this link, it uses slightly diffferent approach for that (for PS) https://stackoverflow.com/questions/21555086/how-to-deploy-a-existing-ssis-package-in-sql-server-2012 – Mike Twc Dec 05 '18 at 20:31
  • ISDeploymentWizard.exe /Silent /ModelType:Project /SourcePath:”D:\Deploy\Receive\My_Beautiful_Project.ispac.ispac” /DestinationServer:”.” /DestinationPath:”/SSISDB/My Beautiful Project/My_Beautiful_Project” /ProjectPassword:”SuperSecretPassword” – Henrov Dec 10 '18 at 09:41
  • I looked at that but couldn't find how to include the password – Henrov Dec 10 '18 at 09:44
  • 1
    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 Dec 11 '18 at 02:38
  • The spaces were not the issue, I tested that. The latter woyuld have been a solution too I think but the following turmed out to be the answer: There is no need to declare the following variables $arg1 $arg1a $arg2 $arg3 $arg4 $arg5, just run the following command – Henrov Dec 11 '18 at 07:22
  • 1
    Just realized there is another approach for that, updated my answer – Mike Twc Dec 15 '18 at 19:10
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.

Nathan Rice
  • 3,091
  • 1
  • 20
  • 30
  • 2
    DestinationPath is the destinationpath within the SSIS catalog. I do not think there is a drive involved? – Henrov Dec 10 '18 at 09:39