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

mceclip0.png

 

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]}
IMPORT[WORK_CvrList]=[ME].{SELECT
    [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(
     REPLACE([EndUser VAT Nbr - SAP],'DKK','')
     ,'DK','')AS CvrNumber
FROM[STAGE_CSV_CvrList]}
/*
select * from [] 
*/

DECLARE @CvrNumber ASSTRING
DECLARE @i ASINT

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
 IMPORT[STAGE_CvrRestApi_CvrData]=[CVR_REST_API].{select*from[](PARAMETERS:CVR=@@CvrNumber)}
MOVENEXT[WORK_CvrList]
LOOP
/* end group Load data */
SAVE

 

 

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

Comments

0 comments

Please sign in to leave a comment.