I'm using with success various Rest Connectors to retrieve data from our CRM software (based on CRM api).
One of my connectors is based on a SQL that is generating 3 columns. One of the columns contains data that are recognized as Base64 data.
For example, the output of one record is "Sk9ITiBERUVSRXxORVcgSE9MTEFORA==" when the original data is "JOHN DEERE|NEW HOLLAND"
The SQL looks like :
select COMP.K_COMPANY, COMP.F_SAP, TRACTOR_BRAND = STUFF ( ( select '|'+ (select F_VALUE from LK_BRAND_MARQUE L8 where L8.K_BRAND_MARQUE=BRANDS.F_MARQUE) from COMPANIES COMPANIES,BRAN_COMP, BRANDS where COMPANIES.K_COMPANY = BRAN_COMP.K_COMPANY and BRAN_COMP.K_BRAND = BRANDS.K_BRAND and comp.K_COMPANY=COMPANIES.K_COMPANY FOR XML PATH (''), TYPE).value('.', 'varchar(max)'), 1, 1, '') from COMPANIES COMP, BRAN_COMP where COMP.K_COMPANY = BRAN_COMP.K_COMPANY and comp.k_company='3425' group by comp.k_company, COMP.F_SAP )
I've tried to convert the Tractor_Brand on the SQL side using cast(TRACTOR_BRAND as nvarchar(max)) as TRACTOR_BRAND_text . But the result is the same in the rest connector.
So, I wonder if there is a function I could use in my load statement to be able to decode the Tractor_Brand field from Base64 to string ?
