none
capture the actual error message while executing powershell script from ssis execute process task RRS feed

  • Question

  • Hi, I am using SSDT for visual studio 2013. I am using an execute process task to run a powershell script in a ssis package. When I execute the powershell script from my machine the script runs as expected but from execute process task it fails and not being able to get the exact error message. How to capture the actual error message.

    Arguments: -ExecutionPolicy Bypass -command "\\networkshare\posh\archive.ps1"

    $ErrorLog = "\\networkshare\test\Error.txt"
    
    Try 
    { 
    $srcPath = "\\networkshare\test\sale"
    $destPath = "\\networkshare\test\sale\archive\"
    $ext = Get-ChildItem -Path $srcPath | Where-Object { ($_.Extension -eq ".txt") }
    
    set-alias sz "\\networkshare\test\7z.exe"
    
    foreach ($file in $ext) { 
    $name = $file.name 
    $directory = $file.DirectoryName 
    $zipfile = $name.Replace(".txt",".7z") 
    sz a -t7z "$destPath\$($file.basename).zip" $file 
    }
    
    Read-Host -Prompt "Press Enter to continue"
    }
    
    Catch 
    { 
    "Error: " | Add-Content $ErrorLog 
    } 


    Thank you in advance.


    SQLEnthusiast

    Wednesday, April 12, 2017 9:48 PM

Answers

  • Add a string variable to your SSIS package

    set the Execute Process tasks StandardOutputVariable property to the variable

    You can also use StandardErrorVariable to get some error info

    Check/Parse the variable when your process finishes in a script task, it should hold execution information


    • Edited by sbogar Thursday, April 13, 2017 4:37 PM
    • Marked as answer by CSharp Enthusiast Thursday, April 13, 2017 10:53 PM
    Thursday, April 13, 2017 4:31 PM

All replies

  • Hi CSharp,

    If you would like to execute PowerShell script in SSIS, you can use Execute Process Task or Script Task. For Execute Process Task, ensure that the Excutable property you add the complete path of the PowerShell executable. And in the Arguments property you add the reset of the command. See: Executing a PowerShell script in an SSIS package

    In terms of SSIS, capturing more information that occur at run time, we can configure the SSIS logs, inside the package->click SSIS->logging->configure SSIS logs->enable events.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, April 13, 2017 6:53 AM
    Moderator
  • Hi, Thank you for the response. I have tried using that and doesn't seem to work. All I am trying to do is when I call the Powershell script in execute process task, how can I keep the called Powershell script from closing its command window too quickly before I could read the error when run from SSDT designer.

    I am using the -noexit switch too.

    Arguments: -ExecutionPolicy Bypass -noexit -command "\\networkshare\posh\archive.ps1"

    SQLEnthusiast

    Thursday, April 13, 2017 4:27 PM
  • Add a string variable to your SSIS package

    set the Execute Process tasks StandardOutputVariable property to the variable

    You can also use StandardErrorVariable to get some error info

    Check/Parse the variable when your process finishes in a script task, it should hold execution information


    • Edited by sbogar Thursday, April 13, 2017 4:37 PM
    • Marked as answer by CSharp Enthusiast Thursday, April 13, 2017 10:53 PM
    Thursday, April 13, 2017 4:31 PM
  • Thanks all for your response.

    SQLEnthusiast

    Thursday, April 13, 2017 10:53 PM