Forum Discussion
Incremental load json from Azure Blos Stoarge to Azure SQL
Hello, I am trying to create a pipe in ADF that will allow incremental load of the data from Azure Blob Storage to Azure SQL. Unfortunetlly I've tried many options but since the source of my data i...
Hello Nathan,
Thank you for your idea. I finally managed to find a solution, using Lookup and Set variable to pass the last modified date to the start time (utc). It was not so easy while I was getting an object from the lookup, although the output preview looked like a date. Here is what I used to transform the object into string and extract the datetime I am interested in: Variable = @substring(string(activity('Max_Time').output),34,23) . I hope this will help others.
Thank you for your idea. I finally managed to find a solution, using Lookup and Set variable to pass the last modified date to the start time (utc). It was not so easy while I was getting an object from the lookup, although the output preview looked like a date. Here is what I used to transform the object into string and extract the datetime I am interested in: Variable = @substring(string(activity('Max_Time').output),34,23) . I hope this will help others.
I'm glad you managed to find a solution.
This approach could cause you issues later if the length of the text output changes format in any way. Instead you can navigate the json data directly to get the data you're after.
For example if the data returned from the activity is:
{
"firstrow":{
"date":"2023-09-22T14:40.00"
}
}
Then you will be able to extract just the date by using the formula:
@activity('Max_Time').output.firstrow.date
This approach could cause you issues later if the length of the text output changes format in any way. Instead you can navigate the json data directly to get the data you're after.
For example if the data returned from the activity is:
{
"firstrow":{
"date":"2023-09-22T14:40.00"
}
}
Then you will be able to extract just the date by using the formula:
@activity('Max_Time').output.firstrow.date
- hello Nathan,
You are right, here is how I build it: I am using this first row option in the Lookup Activity, under Settings there a flag for this and I am selecting only the date I am interested in using SQL Max syntax from the SQL table.
The out put Preview of the lookup activity gives me exactly the date I need but passing it trough a set variable activity I see that the output is an object because it gets a lot of stats from the lookup activity run. So I am using the syntax above to extract the date (@substring(string(activity('Max_Time').output),34,23)), since I am narrowing the output with the lookup activity I am thinking that the activity stats output will never change structure.
Thank you for pointing this out, it's it a very good awareness and prevention of future fails of the pipe.
Br,
Mihaela