You are not logged in.

Calling ETL Jobs fom Excel with VBA via WSDL

Dear visitor, welcome to Palo Community Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

tish1

Sage

  • "tish1" started this thread

Posts: 762

Date of registration: Jul 13th 2009

Location: Vienna / Austria

Occupation: Senior Consultant @ Vector SW DV GmbH

  • Send private message

1

Friday, July 6th 2012, 2:02pm

Calling ETL Jobs fom Excel with VBA via WSDL

Hi,

I'd like to run ETL jobs with parameter from Excel Clients without installing any additional software on the client.
My only idea is, to call a job via VBA and WSDL.
Unfortunately I'm not a VBA-Pro at all, so any help is highly appreciated.

Did anyone out there ever solve that problem?

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH

This post has been edited 1 times, last edit by "tish1" (Aug 9th 2012, 8:31pm)

laloune

Sage

Posts: 217

Date of registration: Dec 2nd 2009

Location: Berlin

Hobbies: Born to work :-)

  • Send private message

2

Monday, July 30th 2012, 1:34pm

Hello tish,

the goal would be to use a SOAP query to drive the ETL process. I did it with php and this works fine.

With VBA you can find information here:
http://www.webcontinuum.net/ws_4.aspx

here the function I use in PHP, if it can help:

PHP Source code

 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

function launch_etl_jobs($server, $project, $name, $variables)
{
    $serverstring='http://' . $server . ':7775/etlserver/services/ETL-Server?wsdl';
    try
    {
        $server = @new SoapClient($serverstring, array('exceptions' => true) );

        $type = "jobs";

        $locator = "$project.$type.$name";

        // Check wether the job is already queued
        $p = array("project" => $project, "type" => $type, "name" => $name, "after" => doubleval(0), "before" => doubleval(0), "status" => "0");
        $response = $server->getExecutionHistory($p);
        $return = $response->return;

        if(count($return) > 0)
        {
            echo "Job is already queued!";
        }
        else
        {    
            // Check wether job is already running
            $p = array("project" => $project, "type" => $type, "name" => $name, "after" => doubleval(0), "before" => doubleval(0), "status" => "5");
            $response = $server->getExecutionHistory($p);
            $return = $response->return;

            if(count($return) > 0) {
            echo "Job is running!";
            }
            else
            {      
                // Everything ok - add and start job       
                $response = $server->addExecution(array('locator' => $locator, 'variables' => $variables));
                $return = $response->return;

                //  Check whether there was an error on job initialisation
                $valid = $return->valid;
                if (!$valid) 
                {
                    echo $return->errorMessage;
                }
                $id = $return->id;

                //  Execute job
                $response = $server->runExecution(array('id' => $id));
                $return = $response->return;

                // Check wether there was an error on execution
                $p = array("project" => $project, "type" => $type, "name" => $name, "after" => doubleval(0), "before" => doubleval(0), "status" => "40");
                $response = $server->getExecutionHistory($p);
                $return = $response->return;

                echo $return;

            }
        }
    }
    
    catch (SoapFault $fault)
    {
        echo  "SOAP Fault: (faultcode: {$fault->faultcode}, faultstring: {$fault->faultstring})";
    }
    
    catch (Exception $e)
    {
        echo  $e->getMessage();
    }

}


hope it helps,
laloune

"To understand recursion, one must understand recursion"

Similar threads

Rate this thread