Starting Invantive Data Hub scripts from Windows Task Scheduler

We use Invantive Data Hub to extract data from Exact Online in multiple companies. We can run the script by hand from within Data Hub, but not from the Windows Task Scheduler.

How to repeatedly automatically run scripts with Invantive Data Hub?

You can either create a batch script by hand (see listing below) or you can use the Query Tool to generate a batch file. It is located in the menu option Editor -> Create Data Hub Batch Script. The options are:

Please note at (1) that it is best to first deploy the Data Hub statically so the path does not change on every upgrade due to Microsoft ClickOnce.

To deploy static, start Data Hub in interactive mode, log on and enter:

local deploy static

The manual variant of a Data Hub batch script is something like:

@echo off
rem
rem Script to run an Invantive SQL file with possible Invantive Data Hub scripting language statements.
rem
rem Please note that the encrypted password is bound to the PC used to generate this file.
rem When you want to run this batch file on another PC, please generate a new encrypted password at that PC.
rem
rem Created by Invantive Query Tool on 2/13/2017 2:42:54 PM.
rem
rem $Id: $
rem

set INVANTIVE_PRG=PATH\Invantive.Producer.QueryEngine.exe    
set INVANTIVE_USR=SOMETHING    
set INVANTIVE_ENCRYPTED_PWD=ENCRYPTED-DEVICE-SPECIFIC-PASSWORD    
set INVANTIVE_CONN=Exact Online\Exact Online (nl)    
set INVANTIVE_SQL_FILE=C:\temp\sample.sql    
set INVANTIVE_LOG_FILE=C:\temp\sample.log    
set INVANTIVE_INTERACTIVE=False    
"%INVANTIVE_PRG%" /user:"%INVANTIVE_USR%" /connection:"%INVANTIVE_CONN%" /encryptedpassword:"%INVANTIVE_ENCRYPTED_PWD%" /file:"%INVANTIVE_SQL_FILE%" /logfile:"%INVANTIVE_LOG_FILE%" /logoverwrite:"%INVANTIVE_LOG_FILE_OVERWRITE%" /interactive:%INVANTIVE_INTERACTIVE%

Using Task Scheduler to automate the batch script execution

After creating the batch file, you can let Windows Task Scheduler pick up the batch script on a frequency of choice. Here are the steps to do this:

  1. Open the Task Scheduler and choose 'Create a Basic Task'.

  1. Choose the frequency of the batch script execution.

  1. Choose the time of day when the script should be executed.

  1. Choose 'Start a program'.

  1. Locate your batch script and choose it in the next screen:

  1. Your task is done, one last step: Check the 'Open the Properties dialog for this task when I click Finish' checkbox.

  1. Check the radio button 'Run whether user is logged on or not'.

image

  1. Enter the credentials for the account that is logged in.

image

  1. You are done, click Ok twice to finish the task. You can find the new task and edit its properties in the Task Scheduler Library:

image

  1. In the properties dialog (right-click your task -> Properties) you can change the frequency of the script execution. Go to the Triggers tab and choose the Edit... button.

image

  1. In the Edit Trigger screen you can choose to repeat a task multiple times a day. This can be done under Advanced Settings. Check Repeat task every: ... and choose a frequency and duration.

Note that an operational code of 0 normally means success, 1 incorrect function and 2 file not found.

Please also note that a file not found can indicate that you are using a batch file which calls other batch file without full directory name. When the start in folder is not set correctly or left empty, the batch file will fail. Add a pushd FOLDER , popd to your script.