Search code examples
sqlpowershellssispowershell-5.0executeprocesstask

How to execute a powershell script in Admin mode via SSIS

I have a powershell script getting all the computers from WSUS using PoshWSUS. I manually execute the script after opening Powershell in admin mode.

I have to execute the script using SSIS now. I have inserted Execute Process Task in Control Flow. The executable is set as C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

This is the argument: -NoProfile -ExecutionPolicy ByPass -command ". c:\mypath\GetWSUSList.ps1" -verb runAs

I've tried many others, mostly including in this page: PowerShell: Running a command as Administrator

But none of them worked and still getting Unauthorization error. Any help would be appreciated.

Solution

  • Here is the solution below:

    Step 1: Create a powershell script file. My script.ps1 is:

    import-module poshwsus 
ForEach ($Server in Get-Content $WSUSServers)
{
    & connect-poshwsusserver $Server -port $WSUSPort | out-file $ProcessLog -append
    & Get-PoshWSUSUpdateSummaryPerClient -UpdateScope (new-poshwsusupdatescope) -ComputerScope (new-poshwsuscomputerscope) | Select Computer, LastUpdated | export-csv -NoTypeInformation -append $FileOutput
}

    Step 2: Create a .bat file, let's say it is called RunMyPS1.bat, like below.

    @ECHO OFF
PowerShell -NoProfile -ExecutionPolicy Bypass -Command "& {Start-Process PowerShell -ArgumentList '-NoProfile -ExecutionPolicy Bypass -File ""C:\Scripts\WSUSReport\script.ps1""' -Verb RunAs}"
PAUSE

    Note that using -verb runAs at the end of the argument line is very important here.

    Step 3: Create a Task Scheduler to run your .bat file, named "RunMyBat" for example.

    Open Task Scheduler, click "Create Task" on the right menu. Under General, make sure the checkbox Run with highest priveleges is checked, this is very important. Then navigate to Actions section, add new action by browsing to your .bat file.

    Step 4. Run your task scheduler via SSIS

    Add "Execute Process Task" to your Control Flow. Make sure the executable of the task is set to: "C:\Windows\System32\schtasks.exe" and the arguments is: "/run /TN "RunMyBat" like below.

    enter image description here

    Step 5. Run your SSIS package.

    Important: Note that after the "execute process task" running the task scheduler is triggered, SSIS directly comes to the next step (if any) without waiting the task scheduler completes its process. Therefore, if there is any tasks that will use the output or updated data by your PowerShell script, then insert a "Script Task" and add sleep to ensure that your powershell script is completed.

    System.Threading.Thread.Sleep(120000);