Invoke-Command with ; in script

This topic contains 13 replies, has 2 voices, and was last updated by  Poshoholic 4 years, 4 months ago.

  • Author
    Posts
  • #9196

    staggerlee011
    Participant

    Hi all,

    im trying to run a SSIS package from Powershell using the Invoke-Command

    all the examples i find to do this are calling files, were are we use packages stored in MSDB.

    I have found the following links that im basing things on:
    http://social.technet.microsoft.com/Forums/windowsserver/en-US/94a9402f-956d-4a4e-8a79-6c7acc3a4597/character-escaping-issue-running-dtexec-using-powershell-invokecommand

    When i add my DTEXEC code i get the following error:

    PS O:\> $sb_string = @'
    DTEXEC.EXE /SQL "\"\Example\test\"" /SERVER "\"Server-SSIS\"" /CONNECTION "\"SQL.BI\"";"\"Data Source=PL-SQL;Initial Catalog=example;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\"" /CHECKPOINTING OFF /REPORTING V
    '@
    $sb = [scriptblock]::create($sb_string)
    Exception calling "Create" with "1" argument(s): "At line:1 char:101
    + ... cumenter\"";"\"Data Source=PL-SQL;Initial Catalog=example;Provider=SQLNCLI11.1;I ...
    + ~~~~
    Unexpected token 'Data' in expression or statement.
    At line:1 char:112
    + ... ;"\"Data Source=PL-SQL;Initial Catalog=example;Provider=SQLNCLI11.1;Integrated S ...
    + ~
    The Data section is missing its statement block.
    "
    At line:4 char:1
    + $sb = [scriptblock]::create($sb_string)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ParseException

    Really not sure where to go from here? any help or suggestions? It seems that im having the same sort of problem as the forum user, but where as the fix worked for him its not for me 🙁 so i still have problem with ";"s and other Powershell related commands being seen in the script?

    S

  • #9206

    Poshoholic
    Participant

    You're not escaping your double-quotes properly. To escape double quotes, either use a pair of double-quotes or use the backtick character. For example:

    """Hello""" # Outputs: "Hello"

    "`"Hello`"" # Outputs: "Hello"

    In contrast, using backslashes like you are will just generate an error because PowerShell doesn't treat those as escaped double-quotes:

    PS C:\> "\"Hello\""
    At line:1 char:4
    + "\"Hello\""
    + ~~~~~~~~
    Unexpected token 'Hello\""' in expression or statement.
    + CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnexpectedToken

  • #9212

    staggerlee011
    Participant

    Hi thanks for the reply,

    Im not trying to use the backslashes for anything, This is how the code looks to run a SSIS package from the command line.

    From what the link said i thought the idea of putting the command in a variable was to get around the problem of having / and " in there.

    Any ideas if theres a way around it?

  • #9219

    Poshoholic
    Participant

    The problem is that you cannot create a script block from a string that defines the script if that script cannot be parsed in PowerShell. Your quotation marks in your string used to create the script block are not parsable in their current state.

    $sb_string = @’
    DTEXEC.EXE /SQL “\”\Example\test\”" /SERVER “\”Server-SSIS\”" /CONNECTION “\”SQL.BI\”";”\”Data Source=PL-SQL;Initial Catalog=example;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\”" /CHECKPOINTING OFF /REPORTING V
    ‘@

    If you look at the above string and try to break it down into tokens with PowerShell, you'll end up with a pretty big mess because of the double-quotes. For example, here are the first few tokens in your string that you are trying to use to create a script block:

    DTEXEC.EXE
    /SQL
    "\"
    \Example\test\
    ""
    /SERVER
    "\"
    Server-SSIS\
    ""
    ...

    That is not how you want these to be defined. You want something more like this:

    DTEXEC.EXE
    /SQL
    "\Example\test"
    /SERVER
    "Server-SSIS"
    ...

    All of that said, others seem to have solved this problem already. For example, this link:
    http://www.timvw.be/2011/07/15/launch-dtexec-from-powershell/

  • #9226

    Poshoholic
    Participant

    Also, here's an example that I was able to get to parse properly:

    $sb_string4 = 'DTEXEC.EXE /SQL "\Example\test" /SERVER "Server-SSIS" /CONNECTION "SQL.BI;Data Source=PL-SQL;Initial Catalog=example;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /CHECKPOINTING OFF /REPORTING V'
    $ExecutionContext.InvokeCommand.NewScriptBlock($sb_string4)

    This gave me the following script block:

    DTEXEC.EXE /SQL "\Example\test" /SERVER "Server-SSIS" /CONNECTION "SQL.BI;Data Source=PL-SQL;Initial
    Catalog=example;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /CHECKPOINTING OFF /REPORTING V

    I'm not sure about the validity of that command (I don't have time to dig into that), but I wanted to share an example that parses properly so that you can perhaps use it to modify your own command.

  • #9247

    staggerlee011
    Participant

    well what ever magic is in that line made it work (Ie run.. got a whole back of kerberos things to deal with now!)

    thank you the help, its very much appreciated!

    S

  • #9337

    staggerlee011
    Participant

    i was clearly having a senior moment last week.. I cant get it to run today.. (Im sure i could on Friday!)

    I think im calling it correctly.

    Summary: using the command line on server pl-ssis using the command below works:
    DTEXEC /DTS "\"\MSDB\Example\test\"" /SERVER "\"PL-SSIS\"" /CONNECTION DBConnect;"\"Data Source=PL-SQL;Initial Catalog=example;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\"" /CHECKPOINTING OFF /REPORTING V

    On a remote machine (PSremote is enabled on PL-SSIS) in PoSH i use:

    $sb_string4 = ‘DTEXEC /DTS "\"\MSDB\Example\test\"" /SERVER "\"PL-SSIS\"" /CONNECTION DBConnect;"\"Data Source=PL-SQL;Initial Catalog=example;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\"" /CHECKPOINTING OFF /REPORTING V’

    invoke-command -computername PL-SSIS -scriptblock { $ExecutionContext.InvokeCommand.NewScriptBlock($sb_string4) }

    It completes with no errors. but doesnt run the DTEXEC.

    Thanks for any help 🙂

  • #9338

    Poshoholic
    Participant

    Try this format instead:

    $sb_string4 = 'DTEXEC /DTS "\MSDB\Example\test" /SERVER "PL-SSIS" /CONNECTION "DBConnect;Data Source=PL-SQL;Initial Catalog=example;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /CHECKPOINTING OFF /REPORTING V'
    
  • #9342

    staggerlee011
    Participant

    editing the DTEXEC command breaks the call i need it to do sadly 🙁

    If i copy and paste your new string into a command line on the PL-SSIS it fails with an error

    Option "Source=PL-SQL;Initial" is not valid.

    S

  • #9343

    Poshoholic
    Participant

    Ok, one more trick to work this out.

    Have you tried using the special –% parameter in your DTEXEC calls? When you are calling command line utilities, some of them (such as this one) have unusual characters required in their parameters, including semi-colons, quotes, etc. These characters cause problems for PowerShell's parser. You can work around this by using –% to instruct PowerShell to pass the remaining arguments in as is, without parsing (therefore ignoring how it would normally handle quotes, semi-colons, etc.).

    So, try this command (even without the script block creation):

    DTEXEC –% /DTS “\”\MSDB\Example\test\”" /SERVER “\”PL-SSIS\”" /CONNECTION DBConnect;”\”Data Source=PL-SQL;Initial Catalog=example;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\”" /CHECKPOINTING OFF /REPORTING V

  • #9393

    staggerlee011
    Participant

    Hey sir,

    im getting the same thing. It runs. takes a few seconds to complete, but doesnt show any results of it running (and the package doesnt run)

    can i confirm im running it like you suggested.

    $sb_string4 = 'DTEXEC –% /DTS “\”\MSDB\Example\test\”” /SERVER “\”PL-SSIS\”” /CONNECTION DBConnect;”\”Data Source=PL-SQL;Initial Catalog=example;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\”” /CHECKPOINTING OFF /REPORTING V'

    invoke-command -computername PL-SSIS -scriptblock { $ExecutionContext.InvokeCommand.NewScriptBlock($sb_string4) }

    Thanks for the help!

  • #9395

    Poshoholic
    Participant

    Oh, I see one problem that you'll need to correct. Instead of this:

    invoke-command -computername PL-SSIS -scriptblock { $ExecutionContext.InvokeCommand.NewScriptBlock($sb_string4) }

    You want to do this:

    invoke-command -computername PL-SSIS -scriptblock ( $ExecutionContext.InvokeCommand.NewScriptBlock($sb_string4) )

    The only difference is in the brackets. In the former, you're invoking a script block that in turn creates a script block (but doesn't invoke it). In the latter, you're invoking a script block that you create.

    Also regarding the command line for DTEXEC, make sure you are entering –% correctly (as in dash, dash, percent). It doesn't appear to be two dashes on these forums, but it is two dashes followed by a percent sign.

  • #9397

    staggerlee011
    Participant

    its a Christmas miracle!

    it works (well the job fails, but thats my bad not yours!) you are a jedi sir! ty...

    Working code!

    $sb_string4 = ‘DTEXEC –% /DTS "\"\MSDB\Example\test\"" /SERVER "\"PL-SSIS\"" /CONNECTION DBConnect;"\"Data Source=PL-SQL;Initial Catalog=example;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\"" /CHECKPOINTING OFF /REPORTING V’

    invoke-command -computername PL-SSIS -scriptblock ( $ExecutionContext.InvokeCommand.NewScriptBlock($sb_string4) )

    I do have a couple of follow up questions for you if you don't mind.

    Running the below in Posh 3 works. In Posh 2 i get:

    Exception calling "NewScriptBlock" with "1" argument(s): "Unexpected token 'data' in expression or statement."
    At line:4 char:99
    + invoke-command -computername PL-SSIS -scriptblock ( $ExecutionContext.InvokeCommand.NewScriptBlock <<<< ($sb_string4) ) + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException Where can i read up on the alts you did.. never seen –% before and im guessing the InvokeCommand.NewScriptBlock is either Posh 3 or using .net? Thanks for the help and sticking it with it... its awesome to have it working! thankyou!!!

  • #9413

    Poshoholic
    Participant

    Hooray, I'm glad it's working now! 🙂

    You're right, –% is a PowerShell 3+ feature, and that is why your script doesn't work in PowerShell 2.0 and earlier versions.

    In PowerShell 3, take a look at this help file:

    Get-Help about_parsing

    In particular, the section labelled "STOP PARSING". That explains why –% is needed and how it works.

    Also for future reference, anytime you want to find help on anything and you don't know where to look, PowerShell does support searching help by keyword. For example:

    PS C:\> get-help '--%'
    
    Name                              Category  Module                    Synopsis
    ----                              --------  ------                    --------
    about_Escape_Characters           HelpFile                            Introduces the escape character in Windows Pow...
    about_Parsing                     HelpFile                            Describes how Windows PowerShell parses commands.
    about_Special_Characters          HelpFile                            Describes the special characters that you can ...
    

    The other two files (about_Escape_Characters and about_Special_Characters) only contain a small blurb about the stop parsing token though, referring readers to about_Parsing for more information.

You must be logged in to reply to this topic.