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>

Inserting space between OneNote notes

When I needed space at the top of a OneNote page that already had a bunch of notes I used to go through the pain of moving each note around to create more space. However, today I found this cool menu option under the “Insert” menu option called “Extra Writing space”. Use that to insert or reduce space between your notes.

Choose the menu option and move your mouse over your page. You will see either a horizontal or a vertical line appear depending on the location of the mouse cursor over the page. Left-click the mouse button, hold and drag appropriately to add or reduce space on the page. If you perform this operation in the middle of a note I noticed it splits the note into two.

Unable to execute SSIS packages containing script tasks developed in 32 bit and deployed in 64 bit environments

I started to run into this error in the last month or so. I’ve been developing SSIS 2005 packages on my desktop which is an XP Professional and deploying them to Windows Server 2003 R2 Enterprise x64 edition. All was well until recently, according to Microsoft one of the updates broke compatibility on a dll and caused this issue (More details here in the Microsoft KB 931846). There were a few suggestions such as open the packages on the target server and make an update and save them to force recompile. This is not fun when you have multiple packages and in some instances did not do the trick.

My solution which has worked consistently so far is to restart the Integration Services after you deploy the  updated packages on the target server.