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.
echo | time > C:\ExecutedHere.txt
<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>
<package> <job> <script language="VBScript"> Dim WShell set WShell = CreateObject("WScript.Shell") WShell.Run "C:\BatToCall.bat" Set WShell = nothing </script> </job> </package>