Parameter on Powershell Script

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  October 19, 2016 at 8:52 am

  #331052

  I'm having some problems sending parameters to a Powershell script when it includes a dash (-) in it. Here's an example on what I'm trying to do. The script is made to run an SSIS package from Powershell.

  param($dtsx = '')

  function GetDtExecPath {

  $DtsPath = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\120\DTS\Setup').SQLPath;

  $DtExecPath = (Resolve-Path "$DtsPath\Binn\DTExec.exe");

  $DtExecPath;

  }

  function GetDtExecPropertyPathValue() {

  param(

  $PropertyPath = '',

  $Value = ''

  );

  $outPropPath = "\Package.Variables[User::" + $PropertyPath + "].Properties[Value]"

  #"$outPropPath;\`"`"$Value\`"`"";

  #"$outPropPath;$Value";

  "$outPropPath;" + '"' + $Value + '"';

  }

  function RunPackage {

  param(

  $DtExecPath = (GetDtExecPath),

  $File = 'test.dtsx'

  );

  $Params = "/FILE " + [CHAR]34 + $File + [CHAR]34;

  for($i = 0; $i -lt $Args.Length; $i += 2) {

  $PropertyPath = $Args[$i].SubString(1);

  $Value = $Args[$i+1];

  $PropertyPathValue = GetDtExecPropertyPathValue -PropertyPath $PropertyPath -Value $Value;

  $Params += " /SET $PropertyPathValue";

  }

  $FullCommand = '"' + $DtExecPath + '"' + $Params;

  $FullCommand

  &"$DtExecPath" $Params;

  #&"$DtExecPath" "$Params"

  }

  RunPackage -File $dtsx -prmMainScriptFolder "C:\Git\DB_Deployment_to_QA" -prmRallyID "US99999"

  And it's called through this:

  powershell.exe -file "deploy-ssis-package.ps1" -dtsx "C:\Git\SQL-Deployment\SQL-Deployer-eFR.dtsx"

  The problem is that I end up with the following result:

  "C:\Program Files\Microsoft SQL Server\120\DTS\Binn\DTExec.exe"/FILE "C:\Git\SQL-Deployment\SQL-Deployer-eFR.dtsx" /SET \Package.Variables[User::prmMainScriptFolder].Properties[Value];"C:\Git\DB_Deployment_to_QA" /SET \Package.Variables[User::prmRallyID].Properties[Value];"US99999"

  Microsoft (R) SQL Server Execute Package Utility

  Version 12.0.4100.1 for 64-bit

  Copyright (C) Microsoft Corporation. All rights reserved.

  Option "-deployment" is not valid.

  If I run the command generated by Powershell, it runs without a problem. Any ideas on why it's ignoring the quotes when called from PoSh??

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Brandie Tarvin

  SSC Guru

  Points: 172994

  More actions

  October 19, 2016 at 11:17 am

  #1907477

  Have you tried renaming the folders to use underlines instead of hyphens?

  I think the hyphens are the problem. That powershell might see them literally as a switch "notifier" kind of thing. Alternatively, see if you can escape the hyphen.

  EDIT: Or better yet, create a share with no hyphens in it. See if that works.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  October 19, 2016 at 12:41 pm

  #1907504

  Brandie Tarvin (10/19/2016)

  ---

  Have you tried renaming the folders to use underlines instead of hyphens?

  I think the hyphens are the problem. That powershell might see them literally as a switch "notifier" kind of thing. Alternatively, see if you can escape the hyphen.

  EDIT: Or better yet, create a share with no hyphens in it. See if that works.

  I tried to escape the hyphens using the grave accent(`) without luck. I tried using single quotes, double quotes and combinations of both without luck.

  I don't want to create a new path because I might need to ask that the git repositories are created again or renamed. I believe that this might be too complex.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  October 19, 2016 at 1:58 pm

  #1907536

  I'll take a wild punt on $Params = "/FILE " => $Params = " /FILE " perhaps?

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  October 19, 2016 at 5:09 pm

  #1907555

  Change this line:

  $FullCommand = '& "' + $DtExecPath + '" --% ' + $Params;

  Then use

  Invoke-Expression $FullCommand

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]