This blog entry will describe how to start a ETL job from a report. Many of you know this situation, if you want to implement a simple button in a report, which has a function to start an ETL job. So you can start ETL jobs from the report manager. There are many ways how to solve this problem and here is a description of one solution.

First of all, create a new spreadsheet. Now you can insert a button, which will start the macro. On the right side you can select the required information like ETL project name, job name etc. Additionally, you can implement two Combo Boxes, which will list the ETL projects and jobs. The result will look like this:


After you have selected the required information, you can create a new macro. The macro code looks like this:

require library('integrator'); 
  // adapt protocol, host, port here to match your setup
  // wait for job to finish prior returning status
  define('WAIT_FOR_FINISH', true);

  // max execution time of script in seconds used in combination with WAIT_FOR_FINISH
  define('MAX_EXECUTION_TIME', 20);
  $prj = retrieve_variable  ('Project');
  $job = retrieve_variable  ('Job');
  $variables = array(array('name' => 'vColor', 'value' => activesheet()->range("H9")->value));      

  $id = integrator_start_job($prj, $job, $variables, WAIT_FOR_FINISH, MAX_EXECUTION_TIME, '' , 

  if ( $id )
    // if the $id will return true, the ETL job will start 
        return [[ 'actn', 'refreshData' ],
               [ 'msg',"Info" , "Info","Job done"]];
    return __msgbox("Error! ETL Job not started");

You can download a -zip-file with the report including the required macro, as well as the sampleBiker ETL project, by clicking here.

By opening the macro editor you will see three other methods getJobs(), getProjects() and soapConnect(). The first two function will fetch the list of all projects and jobs. Last but not least the method SoapConnect() will establish a connection to the SOAP interface of the ETL integrator.

After assigning the macro to your button, you can try to start for example the default job of the “sampleBiker” project. If all information and parameters have been passed right, the status should be set on “Completed successfully”:


Author: Haris Hota