PowerShell script help

  • Hi Guys,

    I am executing a PowerShell script from SSIS (Execute Process Task). Simple script, Calling PS script and returning a variable value. The process runs successfully when I am running on my local machine. However, when I run on the server, the "SSIS Package" hangs on "Execute Process Task".

    When I run on my local machine, it pops up the PS window and closes after few seconds, and it moves. Is anyone think that would cause the issue to hangs on the server? I am using WindowStyle = Hidden on the "Execute Process Task". Please see below the script that I am using. Any help would be highly appreciated.

    Thank.

    PS Script:-

    parm([Parameter(Mandatory=$true)][string]$p1,

    [string]$p2="false")

    $p1 = "Test_Account"

    if ($env:CIMP_CLINET_HOME -eq $null) { Write-Host "Environment variable CIMP_CLIENT_HOME is not set"; exit }

    $command = $env:CIMP_CLIENT_HOME + '\client\bin64\client64.exe' + '' + $p1 + ' ' + $p2

    $output = Invoke-Expression $command

    $tokens = $output.split( ' ')

    $rc = $tokens[0]

    $userid = $tokens[1]

    $password = $tokens[2]

    return $userid.Trim() + "-!-"+$password.Trim()

  • My first step - check the logs.

    My second step - adjust the powershell script to write to a debug file so you can see where it is getting stuck.  That can be done pretty easily with a few write-host commands and >>'s to redirect the output to a file.

    My thought here is I don't see any place where it would get stuck UNLESS client64.exe is having problems being run as the SQL Service account for some reason and that application is causing things to pause.  What I mean by that is if the application is waiting for user input OR requires a GUI window to load or anything like that it MAY cause problems being run by a user who is not logged in (ie a service account).  Or if it requires elevation (admin permissions to run) and UAC is enabled OR the SQL service account isn't an admin, you may be getting an error in a window presented to the SQL service.

    Knowing where it is stuck will help you diagnose what the problem is.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • I have to disagree with Brian - the first step is to review why you are calling out to Powershell in the first place.  Based on the snippet of code you provided, there is nothing there that cannot be done better using a script task.  And - using a script task provides for better error handling and debugging capabilities.

    A script task also is specifically built to allow passing in parameters using SSIS variables - and returning the value into an SSIS variable.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Hi Jeff,

    By chance, do you have an example that I can take a look at in the script task calling the .exe file and pass and return the variable value?

    Thanks, Brian, for your help too. The SQL account has excess. I can run the PS Script on the server and pass the variable value, and it returns some value, so that part is working.

     

  • No - I don't have an example available, but there are many available if you search for C# start process.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • rocky_498 wrote:

    Hi Jeff,

    By chance, do you have an example that I can take a look at in the script task calling the .exe file and pass and return the variable value?

    Thanks, Brian, for your help too. The SQL account has excess. I can run the PS Script on the server and pass the variable value, and it returns some value, so that part is working.

     

    You can try this, call the exe itself directly from the execute process task instead of through a powershell script.  That won't help with the return values but it should tell you if the exe just doesn't like executing in that context.

  • If the password to the SSIS service account is known to you, I would log in as that account and do a test run of the powershell as the SSIS Service account.

    Also, does the application client64.exe require elevated permissions?  If so, does the UAC prompt come up asking you to run the application elevated?  If it does, that could be why you it is getting stuck.  Also, if the client64.exe application needs any GUI to load, that could be why it is getting stuck too.

    Another thought - when you say that the SSIS package doesn't run and gets stuck, is it getting stuck when you run it in visual studio OR only on the SSIS server? Visual Studio should be running it as you, SSIS will run it as the service account.

    And my last thought - is that powershell script signed?  If it is unsigned AND you don't have powershell set to run untrusted scripts as a computer level policy OR at the SSIS service account level but you do on your account, it could be you need to change that policy.  This could be verified by logging in as the SSIS service account.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 7 posts - 1 through 6 (of 6 total)

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