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.
- On the remote server perform the steps as detailed in this Microsoft article Setting up Remote WSH.
- 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.
- 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.
- 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.
- 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.)
- 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>