What we are going to explain below is the following via an example.

Example – We are required to run a PowerShell script which will run with elevated privileges.

So in order to complete this we have to complete the following below but use a simple PowerShell script which will get all the FileNames from a Folder.

  • Create a BAT file which can execute the PowerShell script.
  • Create a Scheduled Task which in turn will call the BAT file.
  • Create an SSIS Package which will call the Scheduled Task

NOTE: The reason that we did this above, was to ensure that we could automate the snapshotting of the database using SSIS. This would enable us to get a snapshot of the data on a required schedule.

Changing the UAC

Below is a quick step to disable the UAC, as I think that this might affect the later steps when the BAT file is trying to run the PowerShell script.

NOTE: You will need to be an Administrator on the Server to turn this off.

  1. Go into the Control Panel.
  2. Then search for UAC.
  3. Then click on the Change User Account Control settings as shown below:
  4. Change it to Never Notify as shown below:
  5. Then click
    Ok
  6. It will ask you one last time if you want to make the change to the Computer.
    1. Click on Yes

Creating the BAT File which can execute the PowerShell script

In the steps below we are going to explain how we create the BAT file which executes the PowerShell script. And also show that at first we can run the PowerShell script manually to ensure that it works.

Testing the PowerShell Script

  1. The first thing to test is to ensure that the PowerShell script actually runs and completes as it should.
  2. Click on Start and then type in PowerShell and from the search results right click on Windows PowerShell ISE and select Run as Administrator
  3. Now when the PowerShell ISE opens you can see from the Window at the top that you are running as Administrator
  4. If you want to follow our example you can then copy and paste the following PowerShell script

    Get-ChildItem
    -Path
    ‘C:\Program Files\Microsoft SQL Server\MSSQL11.PRODBI\MSSQL’
    -Recurse
    |
    Export-Csv
    -Path
    ‘C:\Program Files\Program Files.csv’
    -Encoding
    ascii
    -NoTypeInformation

    1. What we are doing above is to get the File Names for all the files and folders under Program Files and then our SQL Server MSSQL Folder.
    2. And then export it to a CSV.
    3. As with our example once run you should see the CSV file as shown below:
  5. Now we know that our script runs we can now save it to a location on our Server.
  6. As with our example we saved it to a new folder on the C:Drive
  7. We will now use this in the next steps to create the BAT File.

Creating the BAT file which will run the PowerShell script.

In the steps below we will be creating the BAT file which will run the PowerShell script.

  1. Open up Notepad or Notepad++ and copy in the following, which will be explained below:
    1. c:\windows\system32\WindowsPowerShell\v1.0\powershell.exe
      -ExecutionPolicy ByPass -command
      “. ‘C:\XIOSnapshot\Export-MSSQL-Files.ps1′”
    2. What we are doing above is to first put in the full location of PowerShell highlighted in GREEN
    3. Next we are putting in some parameters for PowerShell, which is to bypass the execution policy and the command that is to be run highlighted in PURPLE
    4. And then the final part is where we are calling the PowerShell script which we created above. It is highlighted in RED
      1. NOTE: In order to run the command, we have to put it all in double quotes, as well as it has to have a period in order for it to be executed.
    5. Now we can save this BAT File in the same location with a name.
    6. As per our example we saved it with the following:
  2. Now what we will need to do is to run the BAT file from the command line to test that it all works.
  3. You can now open command prompt and run the bat file as we have shown with our example below:
    2. NOTE: You can see that we are not logged into the Command prompt as Administrator.
    3. We can also see that it completed successfully.
    4. And finally if we look on the file system we can see the file got created.
  4. One final thing to note, is that this will run when we execute the BAT file as we have administrator privileges.

Creating the Scheduled Task which will call the BAT File

Now we are going to create the Scheduled Task which will call the BAT File.

  1. Go into Server Manager, then click on Tools and select Task Scheduler
  2. Once this opens on the right hand side click on Create Task…
  3. The first thing that you have to give your task is a name
    1. As with our example we are going to give it the name as shown below:
    2. Now a VERY important step
      is under Security options make sure to select the Tick box as shown below:
      2. NOTE: With this it means that it will run with the highest privileges and will run as Administrator context
    3. Next if you are going to run this via SQL Server Agent, you are going to have to put in the same domain account
      context that SQL Server Agent Runs Under.
      1. NOTE: When you click ok for the creation of the Scheduled Task it will prompt for the password.
  4. You can leave Triggers blank, because we are going to execute this via SSIS.
  5. Click on Actions
    1. Then click New
    2. Then configure the New Action as shown below, with an explanation afterwards.
      2. Here we are just running our BAT File.
    3. Click Ok.
  6. You can now click Ok for this Scheduled Task to be created.
  7. NOTE: When you click ok for the creation of the Scheduled Task it will prompt for the password.
  8. The final step is to then test the scheduled task by running it within Task Scheduler.
    2. Click on Run
    3. You will briefly get the command prompt Window popping up, which you will not see when running via SSIS.
    4. To confirm this you can view the Last Run Result in Task Scheduler as shown below:
    5. As well as the file on the file system:
  9. Now we are done with the Task Scheduler, calling the BAT file, which is then calling the PowerShell script.

Automating the running of the Scheduled Task in SSIS

In the steps below we are going to show how to automate the running of the Scheduled Task within SSIS

  1. Either create a new SSIS Project or create a new SSIS Package.
  2. Then drag in the Execute Process Task into the Control Flow.
  3. Double click to go into the Properties.
    1. We gave it the following name:
    2. Then click on Process to put in the details to call the Scheduled Task
    3. Now what you will see below is the details to run the Scheduled Task which will be explained afterwards:
      2. From the above under
        Executable, this is where the Scheduled Task exe is saved on our Windows
        Server 2012.
      3. Then under Arguments is where we specified for the Scheduled Task to firstly run (/run) and then the Task Name (/TN) to run
      4. Then click
        Ok.
    4. You should then see the following in your SSIS Package:
  4. Now to test that it all works, execute your SSIS
    Package.
    1. It should then run successfully and you should see the following:
    2. NOTE: That the SSIS will come back immediately saying that was successful if the command is correct.
    3. So if you have something else that is reliant on this step I would suggest putting in something to delay it going onto the next step.
      1. I did this by using the WAITFOR DELAY in SQL Server.
    4. Now we can see the file being updated as shown below:
  5. The final step is to then deploy this to your SSIS
    Server and create the scheduled task and test it.
  6. NOTE: If you have configured the Scheduled
    Task with the same
    domain
    account as your SQL Server Agent
    account it will succeed.
    1. NOTE: This domain account should not have a password that will change.