question

KarishmaKhubchandani-3654 avatar image
0 Votes"
KarishmaKhubchandani-3654 asked RomanKuzmenko-9804 commented

ADF Salesforce Connector enabling select *

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
@concat('select
from ',item().SOURCE_TBL_NAME,' where SYSTEMMODSTAMP = select convert(date,getdate()-1)')
@concat('select from ',item().SOURCE_TBL_NAME,' where SYSTEMMODSTAMP = YESTERDAY')
@concat('select
from ',item().SOURCE_TBL_NAME,' where SYSTEMMODSTAMP = TRUNC(SYSDATE) -1')
@concat('select fields(ALL) from ',item().SOURCE_TBL_NAME,' where SYSTEMMODSTAMP = YESTERDAY')

ERROR Message :
102256-image.png

however, the following works fine when the columns are specified.
@concat('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?




azure-data-factory
image.png (178.4 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@KarishmaKhubchandani-3654 did my response help? If it answered your question, please mark as accepted answer. If not, let me know how I may better assist.

0 Votes 0 ·

@KarishmaKhubchandani-3654 if you found your own solution, please share here with the community.

0 Votes 0 ·

Hey Karishma,
Could you please suggest how did you get the list of columns for the table? For example I'm getting Opportunity table, how you getting the list of fields?
Thanks.

0 Votes 0 ·

1 Answer

MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered

Hello @KarishmaKhubchandani-3654 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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.