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]}
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.
Comments
Please sign in to leave a comment.