none
Execute powershell script from ssis?

    Question

  • Hi,

    I was trying to use the execute process task to kick off a powershell script.  However, nothing happens when I run in debug (the component turns yellow and stays yellow).  Any idea if what I am trying to do is possible and the proper way to configure it?

    btw, I am using powershell for the remoting capabilites.  I need to execute a bat file on a remote server which runs a process in a legacy program. 

    Update:  When I name the ps1 script file in the executable window, it opens it in notepad.  This would be like the default if you double clicked the file.

    Mark

    Thursday, July 1, 2010 2:06 PM

Answers

  • Ok,

    I have this working now.  Here are the steps for future reference:

    1 - In the executable field put the file path for powershell.exe

    For me, this was located in C:\WINDOWS\system32\windowspowershell\v1.0\powershell.exe

    2 - In the arguments field put the powershell command that you wish to run. 

    i.e. Invoke-Command -Computername Server123 {start-process C:\Test.bat -wait}

    The -wait command will force the task to wait before moving on.  The bat file, or whatever process you might want to execute, including powershell scripts, should be located on the target computer.

    Hope this is helpful to someone.

    Mark

    Thursday, July 1, 2010 3:04 PM
  • To run a PowerShell Script from SSIS package. Add a "Execute Process Task" in SSIS and use the following command text.

    This works just great, plus the "-ExecutionPolicy ByPass" switch will take care of any server script policies.

     

    C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -ExecutionPolicy ByPass -command ". 'L:\Powershell_Script\Script1.ps1' 'param1' 'param2'"

     

    Regards

    Deepak

    Friday, May 6, 2011 5:17 PM

All replies

  • Ok,

    I have this working now.  Here are the steps for future reference:

    1 - In the executable field put the file path for powershell.exe

    For me, this was located in C:\WINDOWS\system32\windowspowershell\v1.0\powershell.exe

    2 - In the arguments field put the powershell command that you wish to run. 

    i.e. Invoke-Command -Computername Server123 {start-process C:\Test.bat -wait}

    The -wait command will force the task to wait before moving on.  The bat file, or whatever process you might want to execute, including powershell scripts, should be located on the target computer.

    Hope this is helpful to someone.

    Mark

    Thursday, July 1, 2010 3:04 PM
  • To run a PowerShell Script from SSIS package. Add a "Execute Process Task" in SSIS and use the following command text.

    This works just great, plus the "-ExecutionPolicy ByPass" switch will take care of any server script policies.

     

    C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -ExecutionPolicy ByPass -command ". 'L:\Powershell_Script\Script1.ps1' 'param1' 'param2'"

     

    Regards

    Deepak

    Friday, May 6, 2011 5:17 PM
  • Thank you for the additional info on the execution policy. 

    My original requirement was for the remote execution of script which is why I used the Invoke-Command cmdlet. 

    Friday, May 6, 2011 5:42 PM
  • THANKS a mil. saved me
    Wednesday, March 25, 2015 5:24 PM
  • Hi Deepak_k,

    Where did you put your command text into? I define the Executable field with C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe and define the Argument with -ExecutionPolicy ByPass -command ". 'L:\Powershell_Script\MyScript1.ps1'".

    It returns an error message says: "[Execute Process Task] Error: In Executing "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" "-ExecutionPolicy ByPass -command ". 'L:\Powershell_Script\MyScript1.ps1'"" at "", The process exit code was "1" while the expected was "0"."

    Do you know why?

    Howie


    Wednesday, April 6, 2016 12:01 AM
  • Hi Deepak,

    I have .bat file

    Where I need to set three variables

    SET /P databasename=Please enter the database name

    SET /P SourceData=

    CHOICE /C:YN /N /M

    I have created 3 variables for above arguments I need to pass the value to them. How to do it in SSIS?

    Friday, May 6, 2016 4:04 PM