How to execute a powershell script in Execute Process Task

Question

Post reply

How to execute a powershell script in Execute Process Task

dndaughtery

SSChampion

Points: 11290
More actions
July 21, 2016 at 2:17 pm

#320106

Hey guys, I read a tutorial on how to execute a powershell script in an execute process task here:
http://www.sqlservercentral.com/articles/execute+process+task/129996/
I've followed the instructions for setting the Arguments in the expressions and my expression script is as follows:
"-ExecutionPolicy Unrestricted -File " + @[User::PowerShell_Script_Dir] + "\\Get-00094_ReSubFiles.ps1"
the name of my powershell script is Get-00094_ReSubFiles.ps1
The expression evaluates in the expression designer but I keep getting the message at the bottom saying "An executable file is not specified". What am I doing wrong?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

What did you specify for the 'Executable' property, exactly?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

dndaughtery SSChampion Points: 11290 More actions · Answer 2

dndaughtery

SSChampion

Points: 11290

July 21, 2016 at 2:25 pm

#1891456

I don't have anything set in the process section.

dndaughtery SSChampion Points: 11290 More actions · Answer 3

I am using a UNC directory in the ssis variable used in the expression

dndaughtery SSChampion Points: 11290 More actions · Answer 4

The expression evaluates to

-ExecutionPolicy Unrestricted -File"\\MyServer\Folder1\Folder2\My_Scripts\Get-00094_ReSubFiles.ps1"

Phil Parkin SSC Guru Points: 246985 More actions · Answer 5

dndaughtery (7/21/2016)
I don't have anything set in the process section.

Huh? In the Execute Process Task Editor, you will find an attribute called 'Executable'. That needs to contain the path to powershell.exe (as is described in the link which you provided).

How will the EPT know what to execute, if you do not give it an executable?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

dndaughtery SSChampion Points: 11290 More actions · Answer 6

dndaughtery

SSChampion

Points: 11290

July 21, 2016 at 2:43 pm

#1891464

DOH!

Thanks Phil

dndaughtery SSChampion Points: 11290 More actions · Answer 7

When I try to run the execute process task I get the following error:

The specified executable is not a valid application for this OS platform

Is there something I need to install on the server I'm trying to run it in?

dndaughtery SSChampion Points: 11290 More actions · Answer 8

I found online that powershell is built into Windows 2012 Server R2 so I don't understand why I'm getting this error

dndaughtery SSChampion Points: 11290 More actions · Answer 9

I changed the executable to C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe in the process tab of the execute process task and now getting a different error:

"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" "-ExecutionPolicy Unrestricted -File"\\MyServer\Myfolder\FolderA\Powershell_Scripts\Get-Files.ps1"" at "", The process exit code was "1" while the expected was "0".

Phil Parkin SSC Guru Points: 246985 More actions · Answer 10

dndaughtery (7/22/2016)
I changed the executable to C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe in the process tab of the execute process task and now getting a different error:
"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" "-ExecutionPolicy Unrestricted -File"\\MyServer\Myfolder\FolderA\Powershell_Scripts\Get-Files.ps1"" at "", The process exit code was "1" while the expected was "0".

Try running this in a .bat file, outside of SSIS. You may get better error messages.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

dndaughtery SSChampion Points: 11290 More actions · Answer 11

Running the powershell script manually did uncover some issues with the powerschell script. I had gotten a copy of this from another developer who had used directories on his laptop that I didn't have access to. I corrected them and now I can run the powershell script manually with no errors. I now have my execute process task as follows and still getting the following error:

[Execute Process Task] Error: In Executing "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" "-ExecutionPolicy Unrestricted -File"C:\Powershell Scripts\Get-00253_ReSubFiles.ps1"" at "C:\Windows\System32\WindowsPowerShell\v1.0", The process exit code was "1" while the expected was "0".

EXEcute process task settings:

Executable: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Arguments: -ExecutionPolicy Unrestricted -Command "C:\Powershell Scripts\Get-00253_ReSubFiles.ps1"

WorkingDirectory: C:\Windows\System32\WindowsPowerShell\v1.0

Any ideas where I'm going wrong?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 12

dndaughtery (7/26/2016)
Running the powershell script manually did uncover some issues with the powerschell script. I had gotten a copy of this from another developer who had used directories on his laptop that I didn't have access to. I corrected them and now I can run the powershell script manually with no errors. I now have my execute process task as follows and still getting the following error:
[Execute Process Task] Error: In Executing "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" "-ExecutionPolicy Unrestricted -File"C:\Powershell Scripts\Get-00253_ReSubFiles.ps1"" at "C:\Windows\System32\WindowsPowerShell\v1.0", The process exit code was "1" while the expected was "0".
EXEcute process task settings:
Executable: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Arguments: -ExecutionPolicy Unrestricted -Command "C:\Powershell Scripts\Get-00253_ReSubFiles.ps1"
WorkingDirectory: C:\Windows\System32\WindowsPowerShell\v1.0
Any ideas where I'm going wrong?

If you try putting the command line from the error message, ie, this

"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" "-ExecutionPolicy Unrestricted -File"C:\Powershell Scripts\Get-00253_ReSubFiles.ps1"" at "C:\Windows\System32\WindowsPowerShell\v1.0"

into a .bat file and running it, does it work?

It's been a while, but I seem to remember the EP task 'helpfully' adding its own quotes around some of the items, and in the process creating a syntax error. Just an idea.

--Edit: fixed typo

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

dndaughtery SSChampion Points: 11290 More actions · Answer 13

Yeah, the bat file ran fine. Should I remove the dbl quotes from my Execute process values?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 14

dndaughtery (7/26/2016)
Yeah, the bat file ran fine. Should I remove the dbl quotes from my Execute process values?

I've run out of ideas, sorry.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel