REST CONNECTOR - decoding MIME64/BASE64 SQL data

patricesalem — Tue, 08 Dec 2020 08:07:36 GMT

Hello

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 ?

thanks

topic REST CONNECTOR - decoding MIME64/BASE64 SQL data in Connectivity & Data Prep

REST CONNECTOR - decoding MIME64/BASE64 SQL data