Need to automate a Synapse Analytics Pipeline to gather the information from the previous day. every day

Question

I have a Synapse Analytics Pipeline that gather information from SharePoint and put it into a storage account container in Azure. It is a Copy data action. Right now, every time I need to get more data, I need to change the date on Source /Date filter for Start time and End time. I want to automate this, so I don't have to worry about getting the data every day.

I'm not familiar with Synapse, so detailed replies would be appreciated.

Answer 1

Hello Alex Nieto Castro,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you are in need to automate a Synapse Analytics Pipeline to gather the information from the previous day in every day.

You will need to know how to create a Pipeline Parameter for Dates and Set Default Values for Parameters, so and so forth. The example of expressions you will use is similar to this:

StartDate: @formatDateTime(addDays(utcnow(), -1), 'yyyy-MM-dd')

EndDate: @formatDateTime(utcnow(), 'yyyy-MM-dd')

This setup will automate your pipeline to gather data from the previous day every day, without needing manual intervention. Kindly follow the two links below to achieve the purpose:

• https://techcommunity.microsoft.com/blog/microsoft_graph_data_connect_for_sharepo/step-by-step-synapse-gather-a-detailed-dataset-on-sharepoint-sites-using-mgdc/4070563
• https://techcommunity.microsoft.com/blog/microsoft_graph_data_connect_for_sharepo/four-options-for-sharepoint-site-analytics/4076416

However, if you would like to learn how to do it by yourself, Microsoft has provided a training for you in the additional resources and more documents in the Documentation for your use. You will see all these by the right side of this page.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

Answer 2

@Alex Nieto Castro - Thanks for the question and using MS Q&A forum. 

To automate your Azure Synapse Analytics pipeline to gather information from the previous day every day, you can use pipeline parameters and expressions to dynamically set the date filter values. Here's a step-by-step guide to achieve this: 

Create Pipeline Parameters: Go to your pipeline and click on the 'Parameters' tab. Create two parameters: StartDate and EndDate. 

Set Default Values for Parameters: Set the default values for the parameters to dynamic expressions that calculate the previous day's date. 

1. For StartDate, use the expression: @formatDateTime(addDays(utcnow(), -1), 'yyyy-MM-ddT00:00:00Z') 
2. For EndDate, use the expression: @formatDateTime(utcnow(), 'yyyy-MM-ddT00:00:00Z') 

Modify the Copy Data Activity:  Click on the 'Copy data' activity in your pipeline. Go to the 'Source' tab. For the 'Start time (UTC)' and 'End time (UTC)' fields, use the pipeline parameters you created. 

1. For Start time, use: @pipeline().parameters.StartDate 

1. For End time, use: @pipeline().parameters.EndDate 

Add a Trigger to Run the Pipeline Daily: 

1. Go back to the pipeline and click on 'Add trigger' > 'New/Edit'. 
2. Create a new trigger and set it to run every day at your desired time. 
3. Link this trigger to your pipeline. 

Publish Your Changes: 

1. After making these changes, publish your pipeline. 

Hope this helps. Do let us know if you have any further queries.  

------------- 

If this answers your query, do click `Accept Answer` and `Yes` for was this answer helpful. And, if you have any further query do let us know.  

Answer 3

After some trial-and-error testing this was the solution:

No parameters were needed. Since they were the root of the error I was getting.

Start Date: @formatDateTime(getPastTime(3, 'Day')) - to get the date from 3 days ago

End Date: @formatDateTime(getPastTime(2, 'Day')) - to get the date from 2 days ago

Answer 4

@Alex Nieto Castro - I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer. 

Ask: Need to automate a Synapse Analytics Pipeline to gather the information from the previous day. every day

Solution: After some trial-and-error testing this was the solution:

No parameters were needed. Since they were the root of the error I was getting.

Start Date: @formatDateTime(getPastTime(3, 'Day')) - to get the date from 3 days ago

End Date: @formatDateTime(getPastTime(2, 'Day')) - to get the date from 2 days ago

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information. 

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue. 

 ............................................................

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.