Forum Discussion
Incremental load json from Azure Blos Stoarge to Azure SQL
If you are using a copy activity with Blob Storage as a source and Azure SQL as sink you can use these features:
On the source side you can filter by last modified to only include files that have been modified since the last successful run of your pipeline if you store run dates from your pipeline in your SQL server or anywhere else.
Also on the sink side you can copy insert the data using a stored procedure, which will involve creating a table type in SQL and a stored procedure that takes that type as an input. Then that stored procedure can merge new data into your database based on the last modified field your JSON and any other conditions that are important for you. Bear in mind that depending on your SQL server settings NULL != NULL.
I hope this was helpful
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- 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