Incrementally loading multiple tables from Azure sql to Azure datalakes

Question

Incrementally loading multiple tables from Azure sql to Azure datalakes

Zhu, Yueli YZ [NC] 280

Hi,

The documentation regarding incrementally loading multiple tables https://learn.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-multiple-tables-portal

Instead of using stored procedure, I used script activity. But when I lookup the new watermark value from source tables, I always got errors. Please help

User's image

select MAX(CAST(@item().LastModifytime as datetime2)) as NewWatermarkvalue from @{item().table_name}

Following is the error message: 
The expression 'item().LastModifytime' cannot be evaluated because property 'LastModifytime' doesn't exist, available properties are 'table_name, table_schema'.

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2023-10-23T07:49:33.3166667+00:00

Hi Zhu, Yueli YZ [NC], Just checking if below answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

Accepted answer

1 additional answer

Your answer

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2023-10-23T07:49:33.3166667+00:00

Hi Zhu, Yueli YZ [NC], Just checking if below answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

Answer 1

Subashri Vasudevan 11,226

Hi Zhu, Yueli YZ [NC] in your ListAllLookup, I think you are just bringing table_name, table_schema two columns only instead of doing below -

select * from watermarktable where TableName  =  '@{item().TABLE_NAME}'

That is why in the foreach loop you get the mentioned error.

Either you modify your base query to bring all the columns from watermark table or, if all the tables have same date column, you can use below expression directly.

select MAX(CAST(LastModifytime as datetime2)) as NewWatermarkvalue from @{item().table_name}

Please try this and let us know.

Zhu, Yueli YZ [NC] 280 Reputation points

2023-10-11T12:11:20.06+00:00

Thanks for your response. But in the real scenario, I saw the source tables have lastmodified or createddate column, how can I grab the values from theses columns as NewWatermarkvalue from source tables?
Subashri Vasudevan 11,226 Reputation points

2023-10-11T12:51:52.17+00:00

You need to have the column in watermark table.. that says what's the column name to be used for max date. In your case, one row in watermark would say lastmodified for one row and for the other it should createddt
Zhu, Yueli YZ [NC] 280 Reputation points

2023-10-11T16:05:24.4133333+00:00
Yes. I do have a column in a new watermark table which will be used. So the query will be changed to the following? I am new to adf and query.

select MAX(CAST(LastModifytime as datetime2)) as NewWatermarkvalue from @{item().table_name} or select MAX(CAST(CreatedDate as datetime2)) as NewWatermarkvalue from @{item().table_name}

Thanks for your patience.
Subashri Vasudevan 11,226 Reputation points

2023-10-11T17:51:39.48+00:00

Hi, whats the column name in the watermark table . If possible pls post the structure of the watermark table. Ideally the query should be like this if your watermark table column has the name Lastmodifytime

Select MAX(CAST(@item().LastModifytime as datetime2)) as NewWatermarkvalue from @{item().table_name}

And you need to make sure in the very first lookup you so a select * from tablename

Zhu, Yueli YZ [NC] 280

[DatabaseName]
      ,[TableName]
      ,[SchemaName]
      ,[ColumnName]
      ,[WatermarkDate]

These is the struture of the watermark table. ColumnName is the column using to store "LastModifytime""LastModifytime" value. Please advice. Thanks

Zhu, Yueli YZ [NC] 280 Reputation points

2023-10-12T12:55:16.7666667+00:00

Also, I want to clarify the following things:

select * from watermarktable where TableName = '@{item().TABLE_NAME}' is the query to get old watermark value from watermarktable for the LookupOldWatermark activity. This works fine

select MAX(CAST(LastModifytime as datetime2)) as NewWatermarkvalue from @{item().table_name} is the query to get new watermark value from source tables for LookupNewWatermark activity. This doesn't work.

Please advice
Zhu, Yueli YZ [NC] 280 Reputation points

2023-10-12T12:59:20.85+00:00

The fail message for the LookupNewWatermarActiviy is below:
Subashri Vasudevan 11,226 Reputation points

2023-10-12T13:18:43.28+00:00

Hi, please try to use comments instead of answer section .. and since your table has the column name as ColumnName, you should be using below query.

Select MAX(CAST(@item().ColumnName as datetime2)) as NewWatermarkvalue from @{item().tablename}
Subashri Vasudevan 11,226 Reputation points

2023-10-12T13:20:16.7733333+00:00

This might he due to the fact that you are not prefixing the table name with proper schema and database. Pls try to prefix the table with proper schema and db names.
Zhu, Yueli YZ [NC] 280 Reputation points

2023-10-12T13:42:45.3733333+00:00

I used the exact same name for the table provided by microsoft documentation.
Zhu, Yueli YZ [NC] 280 Reputation points

2023-10-12T13:45:47.5566667+00:00

But this is get new watermark value. and should get the columns from source tables. In the source table, the only columns used are LastModifytime and CreateDate

Answer 2

ShaikMaheer-MSFT 38,546 Microsoft Employee Moderator

Hi Zhu, Yueli YZ [NC],

Thank you for posting query in Microsoft Q&A Platform.

Why error:

In ListAllTable(Look up activity) you are getting list of tables as array. You can check same in the output json of look up activity. So basically, you are getting table_name and table_schema columns.
Above array from lookup activity, you are passing it to ForEach Activity to iterate over that table names. So for every iteration you get one item from array, and in the error you will have only table_name and table_schema columns.

But, in your below query, you are trying to take LastModifytime property. Technically item() object not contains that property. Hence you are seeing the error.

select MAX(CAST(@item().LastModifytime as datetime2)) as NewWatermarkvalue from @{item().table_name}

Kindly consider checking below video of mine, where i explained how to pass output of one activity to another activity. That will help you to understand better.

How to read JSON output of one Activity in to another Activity in Azure Data Factory

Kindly correct your query according to avoid this error.

Hope this helps. Please let me know if any further queries.

Please consdier hitting Accept Answer button. Accepted answers help community as well.

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2023-10-20T17:52:46.2666667+00:00

Hi Zhu, Yueli YZ [NC], Just checking if above answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.
Zhu, Yueli YZ [NC] 280 Reputation points

2023-10-23T12:21:33.54+00:00

Thanks for your answer. It's very helpful. In this situation, what can I do to get the LastModifytime column?
Zhu, Yueli YZ [NC] 280 Reputation points

2023-10-23T12:24:52.3166667+00:00

Thanks, I saw the answers before. It helped.

Share via

Incrementally loading multiple tables from Azure sql to Azure datalakes

1 additional answer

Your answer