ADF Salesforce Connector enabling select *

Question

My client wants to load data from Salesforce both bulk and incremental. and wants to make the pipeline dynamic using lookup and foreach to fetch data from all the tables and all the columns from each table.

if the source is SQL or Oracle select * works, however with SOQL it doesn't work.

however, SOQL doesn’t support select *, ADF supports it! And * is expanded to all fields underneath.

Tried select * doing bulk load and it did work. but trying the same with incremental load using where clause isn't working. and giving following errors :

Exact Query listed below with error message :

Tried below queries that couldn't work with select *
@markus.bohland@hotmail.de ('select * from ',item().SOURCE_TBL_NAME,' where SYSTEMMODSTAMP = select convert(date,getdate()-1)')
@markus.bohland@hotmail.de ('select * from ',item().SOURCE_TBL_NAME,' where SYSTEMMODSTAMP = YESTERDAY')
@markus.bohland@hotmail.de ('select * from ',item().SOURCE_TBL_NAME,' where SYSTEMMODSTAMP = TRUNC(SYSDATE) -1')
@markus.bohland@hotmail.de ('select fields(ALL) from ',item().SOURCE_TBL_NAME,' where SYSTEMMODSTAMP = YESTERDAY')

ERROR Message :

however, the following works fine when the columns are specified.
@markus.bohland@hotmail.de ('select ',item().COLUMN_LIST,' from ',item().SOURCE_TBL_NAME,' where SYSTEMMODSTAMP = YESTERDAY')

Any way to make it dynamic - or using select * with where clause via ADF for salesforce?

Answer 1

Hello @Karishma Khubchandani and welcome to Microsoft Q&A.

I'm glad to see you are excited about the ability of Data Factory to support select * on Salesforce source.

The reason you can use select * here is because Data Factory supports use of either SOQL, or SQL-92. These two query languages look very similar, but there are important differences and limitations between them. These differences include how datetimes are used in the where clause.

From your examples, it appears YESTERDAY is part of SOQL, but not part of SQL. Since you are already using a concat expression, try replacing YESTERDAY with the actual date.

I am not sure exactly what should be returned by YESTERDAY , but I do have something you can try.

@formatdatetime(adddays(startOfDay(utcnow()),-1),'yyyy-MM-dd HH:mm:ss')  

I used the above expression to get yesterday's date in a Set Variable activity. It gets the current UTC datetime, then sets the time to start of day, then subtracts one day, and finally formats it for the SQL.

Let me know if this helps.