How to write ADF dynamic expression with SQL statements in multiple levels

Question

I am trying to write ADF dynamic content for filter activity where I need to filter files, having dates in their name, coming from get metadata activity by multiple conditions. I am writing a SQL query which uses activity output in WHERE condition and then provides a DATE as a output. Then I need to select only the files whose date in name is greater or Equals to the SQL query's output DATE.

Again the files which satisfy above condition I need to make sure their extension is either .csv or .zip.

Basically I have 3 types of filenames like - 'a_01-01-2001.csv','b_01-01-2001.csv','c_01-01-2001.csv'. now same file names also with .zip and .ok(filtered out) for each week. Now these files have dates in their name itself. And I have Control Table in SQLDB which holds last_success_run_date for three of the file(which more likely going to same for all). Now based on this last_success_run_date in SQLDB I have to perform Incremental load on FTP source. So using the SQL statement used above I'm checking if [current Item name] of Filter Activity (Not ForEach) has date in it greaterorequal to last_successful_run_date

For which I am writing the dynamic content but it is resulting in error. I don't understand how to write it properly.

I tried writing this

@and(or(endswith(item().name,'.zip'),endswith(item().name,'.csv')),
greaterOrEquals(formatDateTime(substring(item().name,6,10),'yyyy-MM-dd'),
formatDateTime(
select last_success_run_Date from dbo.ADF_Control_Table 
where FileName = '@{substring(replace(item().name,".zip",""),17, sub(length(item().name), 21))}',
'yyyy-MM-dd'
)))

It seems like you are expecting the Filter activity to query a database. You should put the SQL operation into a previous activity, like Lookup, and capture the "last_success_run_Date" value into a variable. Then you can use that variable in your Filter. — Joel Cochran
– Joel Cochran, Commented Mar 8, 2024 at 14:47
But we can have different last_success_run_date for each of the file. I have updated my question with few more details. could you please look into it again. for more clarity. — yash394
– yash394, Commented Mar 8, 2024 at 17:10

score 0 · Accepted Answer · 2024-03-10 15:49:07Z

0

I am assuming that in the activity where you're filtering files (probably a ForEach activity with an If Condition inside), you need to use its output.

You can use a variable last_success_run_Date as a variable or output from a previous activity :

@and(
    or(
        endswith(item().name, '.zip'), 
        endswith(item().name, '.csv')
    ),
    greaterOrEquals(
        formatDateTime(substring(item().name, 6, 10), 'yyyy-MM-dd'),
        formatDateTime(variables('lastSuccessRunDate'), 'yyyy-MM-dd')
    )
)

Update :

I think you will need a Lookup Activity that queries your database to retrieve the last_success_run_date:

 SELECT last_success_run_date FROM dbo.ADF_Control_Table WHERE FileName = 'your condition here'

Then update the expression like below :

@and(
    or(
        endswith(item().name, '.zip'), 
        endswith(item().name, '.csv')
    ),
    greaterOrEquals(
        formatDateTime(substring(item().name, indexOf(item().name, '_') + 1, 10), 'yyyy-MM-dd'),
        formatDateTime(variables('lastSuccessRunDate'), 'yyyy-MM-dd')
    )
)

edited Mar 10, 2024 at 15:49

answered Mar 8, 2024 at 14:54

user9517769

Sign up to request clarification or add additional context in comments.

1 Comment

yash394 Over a year ago

No, I'm not using for each. Basically I have 3 types of filenames like - 'a.csv','b.csv','c.csv'. now same file names just with .zip and .ok(filtered out). Now these files have dates in their name itself. And I have Control Table in SQLDB which holds last_success_run_date for three of the file(which more likely going to same for all). Now based on this last_success_run_date in SQLDB I have to perform Incremental load on FTP source. So using the SQL statement used above I'm checking if [current Item name] of Filter Activity (Not ForEach) has date in it greaterorequal to last_successful_run_date

Collectives™ on Stack Overflow

How to write ADF dynamic expression with SQL statements in multiple levels

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related