Mario Netto’s Tech blog

My notes from the software world

Archive for the ‘Windows Scripting’ Category

Execute remote batch file from SQL job task

Posted by Mario on December 10, 2008

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>

Posted in SQL Server 2005, Windows Scripting | Tagged: | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.