Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
Incremental Dynamic Historical Data Loading by Year and Month: From SQL On-Premise to Azure Blob Storage
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(310.4, 87%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELM%3C/text%3E%3C/svg%3E)
Lucas Medina
20
Reputation points
I Would like to extract data from multiple SQL Server On-premises tables to Azure Blob Storage, focusing on a historical load of data dated before 2024-01-15. For each table, the data should be stored in dynamically created folders, organized by year and month, and following the structure 'year/month/tablename.parquet'. This process should be based on the watermark column instead of using utcNow()
This is my currently pipeline. I am using incremental loading using a watermark table:
inside foreach:
Lookup to get old value:
Lookup to get max value:
Copy activity Source query:
SINK:
Has anyone implemented a similar solution, or can you suggest an approach to achieve this?
Azure Data Factory
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(288, 61%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
phemanth 15,855 Reputation points Microsoft External Staff Moderator2024-01-17T08:29:57.38+00:00 Thanks for posting your question in the Microsoft Q&A forum.
It seems like you’re having trouble with the
currentTimestamp()function not updating theUpdate_Datein your SCD Type 1 implementation in Azure Synapse Analytics. Here are a few things you could check:- Data Type Compatibility: Ensure that the
Update_Datecolumn in your target table is compatible with the data type returned bycurrentTimestamp(). ThecurrentTimestamp()function returns a timestamp, so yourUpdate_Datecolumn should be of a timestamp or datetime data type. - Expression Syntax: Check the syntax of your expression. The correct syntax should be
Modified_Date = currentTimestamp(). If there are any syntax errors, it might prevent theUpdate_Datefrom being updated. - Data Flow Debugging: There might be discrepancies between the data preview output and the actual values inserted into the table after the debug run. It’s recommended to check the data preview output and the actual table values.
- Time Zone Considerations: The
currentTimestamp()function returns the current timestamp in UTC. If yourUpdate_Datecolumn is in a different time zone, this might cause discrepancies.
If you’ve checked all of these and the issue persists, it might be helpful to look at the logs for any errors or warnings. If you’re still having trouble, please provide more details about your pipeline. Hope this helps. Do let us know if you any further queries.
- Data Type Compatibility: Ensure that the
-
Lucas Medina 20 Reputation points
2024-01-17T12:09:21.6766667+00:00 Hi @phemanth , I can not use Azure CDC. I am not using Synapse Analytics. Instead, I am extracting data from SQL on-premises using a self-hosted Integration Runtime (IR). If you prefer, I can share my .json code.
-
phemanth 15,855 Reputation points Microsoft External Staff Moderator
2024-01-18T08:43:27.2733333+00:00 @Lucas Medina
Since you’re using a self-hosted Integration Runtime (IR) and extracting data from SQL Server on-premises, you can still use the watermark method for incremental loads. Here’s a high-level approach:Source Query: In your Copy activity, modify the source query to filter rows based on the watermark column. The watermark column should be a datetime type and should be updated whenever a row is updated. The query should select rows where the watermark column is less than ‘2024-01-15’.
Sink Path: In the sink settings of your Copy activity, you can specify the output path dynamically using system variables. For example, you can use the following expression to create a path like ‘year/month/tablename.parquet’:
@{formatDateTime(pipeline().parameters.watermark,'yyyy')}/@{formatDateTime(pipeline().parameters.watermark,'MM')}/@{pipeline().parameters.tableName}.parquetWatermark Update: After the Copy activity, add an Update activity to update the watermark in your watermark table with the maximum value of the watermark column from the rows that were just copied.
Please note that this is a high-level approach, and you might need to adjust it based on your specific requirements and environment. Always test your changes in a controlled environment before deploying them to production.
-
phemanth 15,855 Reputation points Microsoft External Staff Moderator
2024-01-19T14:40:19.5966667+00:00 @Lucas Medina We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
-
phemanth 15,855 Reputation points Microsoft External Staff Moderator
2024-01-22T06:23:40.2233333+00:00 @Lucas Medina just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sign in to comment
Sign in to answer