Retrieving information from REST Api with loop from input list

This script is a simple example of communication with a REST API using ETL studio.

An input list is read - an for each entry the REST API is called and information retrieved - so the script contains an example of looping through reading a file until a certain value is reached of EOF (end of file).


ETL Flow



Full script

LOAD ASSEMBLY 'TARGIT.Csv.dll'//server
LOAD ASSEMBLY 'TARGIT.Javascript.dll'//server
DATASOURCE[CSV_dk_semicolon]= DOTNET CONNECTION 'TARGIT.CSV.CsvConnection''HasHeaders=true;loadasstring=true;type=local;delimiter=;;encoding=utf-8;culture=da-DK;detectionrowscount=100;skiplines=0;'

DATASOURCE[CVR_REST_API]= DOTNET CONNECTION 'TARGIT.Javascript.JavascriptConnection''script=Tokengoeshere==;;scriptTimeoutSecs=600;LicenseAddress=localhost;LoadAsString=true'
/* group Debug */
DECLARE @DebugInfo asINT
DECLARE @CvrRestApiLoadCounter asINT

/* DebugInfo: 
1 will keep temp,STAGE and WORK tables in final database. 
0 removes temp tables */
SET @DebugInfo =0

/* Specify how many CVR Numbers to look up, during load */
SET @CvrRestApiLoadCounter =3
/* end group Debug */
/* group Load data */
IMPORT[STAGE_CSV_CvrList]=[CSV_dk_semicolon].{select*from[TARGIT REST API\Examples\CVR API\Data\TARGIT DK CVR List.csv]}
    [EndUser VAT Nbr - SAP],
    [EndUser Name - SAP],
    /* Returns a string which replaces all occurrences of string2 in string1 with string3 */
 /* Removes DKK first and then DK */
     REPLACE([EndUser VAT Nbr - SAP],'DKK','')
     ,'DK','')AS CvrNumber
select * from [] 


WHILE[WORK_CvrList].EOF=false and @i < @CvrRestApiLoadCounter
 SET @i = @i +1
 SET @CvrNumber = WORK_CvrList.CvrNumber
 PRINT'Reading CVR data about '+ @CvrNumber
 PRINT'Number '+ @i +' of '+ @CvrRestApiLoadCounter
/* end group Load data */



Note: The REST API in question needs a token which is not included in this example. The input list is not included either.


Was this article helpful?
0 out of 0 found this helpful


1 comment
  • Hi Niels,

    I am a bit confused by the example provided here as the syntax seems significantly different from the template provided when trying to import through Java.

    Why is that and what is the difference?

    // Otherwise use this template:
    const doGetAsync = async (uri, contentType, headers) => {
        return utils.GetAsync(uri, contentType, JSON.stringify(headers))
            .then(success => {
                return JSON.parse(success);
            }, error => {
                return JSON.parse(error);
            }).then(response => {
                if (response.Data && response.Data.Response) {
                    return JSON.parse(response.Data.Response);
                return response.Data;
    var data = await doGetAsync('@@URL@@', "application/json", {});
    jsonStringResult.registerJsonResponse(JSON.stringify(data)); //this line is required to pass the JSON string into the JavaScript driver.

    Just to be clear. It is the JavascriptConnection that doesnt make much sense to me :)

    DATASOURCE[CVR_REST_API]= DOTNET CONNECTION 'TARGIT.Javascript.JavascriptConnection''script=Tokengoeshere==;;scriptTimeoutSecs=600;LicenseAddress=localhost;LoadAsString=true'

Please sign in to leave a comment.