Execute remote batch file from SQL job task

So I was asked if I could execute a batch file after an ETL was complete. The batch file to be executed resided on a remote server. The process currently involves a scheduled SQL job that first executes a SSIS package and then a stored procedure. Here are the steps I performed to be able to achieve this.

  1. On the remote server perform the steps as detailed in this Microsoft article Setting up Remote WSH.
  2. Create a BatToCall.bat batch file that will reside on the remote server. This is the batch file that we are going to execute remotely.
  3. Create a ExecuteRemotely.wsf script file. This script file will reside locally where the task is being executed but when called will perform the stated operations remotely.
  4. Create a Caller.wsf script file that will be called from the SQL job. This script file in turn will call the ExecuteRemotely.wsf script file.
  5. Create a SQL job with a step of type “Operating system (CmdExec)”. In the command, type “CScript Caller.wsf”. (The CScript command can be run from the command-line as well.)
  6. Execute the SQL job with appropriate permissions in place should execute the remote batch file.

Here is sample code for the above mentioned files.

BatToCall.bat


echo | time > C:\ExecutedHere.txt

Caller.wsf

<package>
<job>
<script language="VBScript">
set oController = CreateObject("WSHController")
set oProcess = oController.CreateScript("C:\ExecuteRemotely.wsf", "\\RemoteServerName")
WScript.ConnectObject oProcess, "remote_"

oProcess.Execute
While oProcess.Status <> 2
   WScript.Sleep 50
WEnd
WScript.Echo "Done"

Sub remote_Error
    Dim theError
    Set theError = oProcess.Error
    WScript.Echo "Error " & theError.Number & " - Line: " & theError.Line & ", Char: " & theError.Character & vbCrLf & "Description: " & theError.Description
    WScript.Quit -1
End Sub

</script>
</job>
</package>

ExecuteRemotely.wsf

<package>
<job>
<script language="VBScript">
Dim WShell
set WShell = CreateObject("WScript.Shell")
WShell.Run "C:\BatToCall.bat"
Set WShell = nothing
</script>
</job>
</package>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s