UrlLogger to Packages to Reports

Big Picture
I am capturing data from Gravity Forms , which is sending the same to URL logger as webhook, everytime an entry is received for few forms, I want to create an pdf, upload the same to Gdrive.

For these discussion I want to focus on creating the PDF file, once URL logger receives the webhook
1. Using Script function , I can trigger relevant ETL package
2. Where I am stuck currently is not able to establish a workflow on how to move items from URL_logger event table to a package smoothly , then pass the values from json to a report package.
3. An example would be greatly appreciated. 


{
  "candidate _ID": "00157",
  "name": "Nilam Shinde",
  "mobile": "56321456985",
  "position": "hr",
  "related_recruiter": "portal",
  "is_filled_by_recuriter": "",
  "resume": " krishnabeads.com/wp-content/uploads/grav...orms/28Shinde_CV.pdf ",
  "parent_entry_id": false,
  "recruiter_email": "This email address is being protected from spambots. You need JavaScript enabled to view it.",
  "recruiter_name": "workindia",
  "join_date" : "07-19-2024",
  "recruiter_code": "099"
}


I am hoping to create simple PDF such as below  ( couldn't insert PDF) 

Can you tell me your URL Logger repository type?

Peter.Jonson wrote:

Can you tell me your URL Logger repository type?

I didn't understand the question properly , I am using Microsoft SQL  2019

1 You need to pass Event id to the package from url logger as a command line parameter

const fs = require("fs");
const { spawn } = require("child_process");
try {
  const WORKING_DIRECTORY = "C:\\Program Files\\DB Software Laboratory\\Advanced ETL Processor Enterprise\\";
  const fileToExecute = "C:\\Program Files\\DB Software Laboratory\\Advanced ETL Processor Enterprise\\aetlcl.exe";
  const objectId = 8687
  const parameters = [objectId,logId];
  logger.info(parameters);
  spawn(fileToExecute, parameters, { cwd: WORKING_DIRECTORY, detached: true });
  return 200;
} catch (err) {
  logger.error(`Caught an error: ${err.message ? err.message : err}`);
  return 500; // Any error caught here results in a 500 status
}

2 Than you need read this command line parameter and save it in to the variable

Here is the script:

begin
 SetVariable('<EventId>',CommandLineParameter(2));
 Result:=True;
end;

3 Next step is to pass this variable as a parameter to the report

Please read the following article

www.etl-tools.com/wiki/advanced-etl-processor/advanced/reports/

4 Since the data is in JSON format we have to covert it into proper dataset using SQL so it can be used as the source for the report.

DECLARE @json NVarChar(max)
select top 1 @json=text_field_02 from event_log 

select * from OpenJson(@json)
WITH ("candidate _ID" VARCHAR(100) '$."candidate _ID"',
"name" VARCHAR(100) '$.name',
"mobile" VARCHAR(100) '$.mobile',
"related_recruiter" VARCHAR(100) '$.related_recruiter',
"is_filled_by_recuriter" VARCHAR(100) '$.is_filled_by_recuriter',
"resume" VARCHAR(100) '$.resume',
"parent_entry_id" VARCHAR(100) '$.parent_entry_id',
"recruiter_email" VARCHAR(100) '$.recruiter_email',
"recruiter_name" VARCHAR(100) '$.recruiter_name',
"join_date" VARCHAR(100) '$.join_date',
"recruiter_code" VARCHAR(100) '$.recruiter_code'
)

 

I have attached the example for your convenience

.

Peter.Jonson wrote:

I have attached the example for your convenience

.

File Attachment:

File Name: Example_20...7-07.zip
File Size:2 KB

This was really needed for me . Thank you for taking time to put this together. 
Embarrassing part - How do I import the fp3 report you've given me. Please see my embarrassing tries