UPDATE (08 AUG 2016): I have updated this to use a SQL Server Agent Job instead of the Task Scheduler which proved to not be reliable.

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

Example – We are required to run a PowerShell script which will snapshot a database using Xtreme IO from EMC. In order for this to run it has to be run as Administrator due to it having to drop and re-create the disk drives in the Operating System layer.

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.

Using SQL Server Agent Job to run BAT Files

Below is the process which will actually enable you to run your BAT files under the correct CMD.EXE Context

  1. In order to get this to work, we had to use a combination of SQL Server Agent steps and SSIS steps, which is explained below
    1. First we then needed to use our BAT File to run the PowerShell script which would snap the data.
      1. We configured this step in the SQL Server Agent Job properties as shown below:
      2. As you can see from above we change the Type to be “Operation system (CmdExec)”
        1. The reason for this is that this runs the CMD.EXE in the correct context.
      3. And then we put in our BAT file which calls the PowerShell script which we created earlier.
    2. Next we then ran the BAT file which modified the folder permissions.
      1. We configured this step in the SQL Server Agent Job properties as shown below:
      3. As you can see from above we change the Type to be “Operation system (CmdExec)”
        1. The reason for this is that this runs the CMD.EXE in the correct context.
  2. You can then schedule your SQL Server Agent job and as I tested it did run successfully.