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

Alex Nieto Castro 0

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.

User's image

Sina Salam 12,816 Reputation points

2024-11-13T16:58:15.45+00:00
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.

1 answer

Ganesh Gurram 1,510 Reputation points Microsoft Vendor

2024-11-13T17:49:52.8766667+00:00
@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.

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

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.

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

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

Add a Trigger to Run the Pipeline Daily:

Go back to the pipeline and click on 'Add trigger' > 'New/Edit'.

Create a new trigger and set it to run every day at your desired time.

Link this trigger to your pipeline.

Publish Your Changes:

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.
Please sign in to rate this answer.
Alex Nieto Castro 0 Reputation points

2024-11-13T22:20:40.9266667+00:00

Hello Ganesh Gurram, this looks great. After following the steps, I got this error:

{ "errorCode": "2200", "message": "ErrorCode=UserErrorOffice365DataLoaderError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Office365 data loading failed to execute. office365LoadErrorType: PermanentError. Property SourceDataSet.startTime failed to be deserialized. Please correct the input and retry.,Source=Microsoft.DataTransfer.ClientLibrary,'", "failureType": "UserError", "target": "Copy data1", "details": [] }

Your help is very appreciated.

Thank you,

Alex

Ganesh Gurram 1,510 Reputation points Microsoft Vendor

2024-11-13T23:17:16.3933333+00:00

@Alex Nieto Castro - By following all the steps provided in the above answer, we are able to automate the Azure Synapse Analytics pipeline to gather information from the previous day every day successfully.

The error you're encountering suggests that there is a problem with how the startTime and endTime parameters are being deserialized in the Office 365 dataset. This typically happens if the format or type of the dynamic content being passed to these parameters is not correct.

Steps to Resolve the Issue:

Ensure Correct Date Format:

The date format should be in the ISO 8601 format. The expressions provided earlier are already in this format, but we need to double-check that they are being passed correctly.

Check Parameter Data Types:

Ensure that the StartTime and EndTime parameters are of the String type in your pipeline.

Update Dataset Source Settings:

In your dataset, ensure that the startTime and endTime fields are correctly referenced and receiving the pipeline parameters.

Review Parameter Definitions:

Go to the pipeline parameters and ensure that StartTime and EndTime are defined as String.

Modify Dynamic Content Expressions:

Update the expressions to ensure they are returning strings. You can use the string() function to explicitly convert the date to a string format.

For StartTime:
@string(formatDateTime(addDays(utcnow(), -1), 'yyyy-MM-ddT00:00:00Z'))

For EndTime:
@string(formatDateTime(utcnow(), 'yyyy-MM-ddT00:00:00Z'))

Update Dataset:

Open your dataset (e.g., Microsoft365Table1).

Ensure that the startTime and endTime fields are correctly mapped to the pipeline parameters.

Go to the Parameters tab of your dataset and create parameters named startTime and endTime if they don't already exist.

In the Connection tab or wherever you set the startTime and endTime in the dataset, bind these fields to the dataset parameters.

In the pipeline, ensure that the dataset parameters are linked to the pipeline parameters. For example:

Dataset startTime parameter: @pipeline().parameters.StartTime

Dataset endTime parameter: @pipeline().parameters.EndTime

Test the Pipeline:

After making these changes, validate and debug the pipeline again to ensure it runs without errors.

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

Ganesh Gurram 1,510 Reputation points Microsoft Vendor

2024-11-14T15:50:47.58+00:00

@Alex Nieto Castro - Just checking in to see if the above answer helped. 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.

Alex Nieto Castro 0 Reputation points

2024-11-14T20:23:24.2433333+00:00

Hello, I double check everything again and also tried the step 5: Modify Dynamic Content Expressions and I'm getting the same error:

Property SourceDataSet.startTime failed to be deserialized

Ganesh Gurram 1,510 Reputation points Microsoft Vendor

2024-11-15T13:30:31.1733333+00:00

@Alex Nieto Castro - Thank you for your patience. In order to assist you better, could you please provide more detailed information about the error you are encountering?

Please copy and paste the full error message you are seeing, including any additional details provided.

Let us know at which step you are encountering the error. If possible, please provide screenshots.

Meanwhile, as you mentioned you are not familiar with Synapse, please go through this MS documentation for better understanding: Get Started with Azure Synapse Analytics

Ganesh Gurram 1,510 Reputation points Microsoft Vendor

2024-11-18T06:05:55.5566667+00:00

@Alex Nieto Castro - 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.

Alex Nieto Castro 0 Reputation points

2024-11-18T15:19:10.08+00:00

This is the error:

These are the parameters I'm using per suggested below:

startdate: @formatDateTime(addDays(utcnow(), -4), 'yyyy-MM-ddT00:00:00Z')

endate: @formatDateTime(addDays(utcnow(), -3), 'MM/dd/yyyy T00:00:00Z').

I also tried this parameter values:

@string(formatDateTime(addDays(utcnow(), -4), 'MM/dd/yyyy'))

@string(formatDateTime(addDays(utcnow(), -3), 'MM/dd/yyyy'))

This is the Source tab where I'm referring the parameters:

@pipeline().parameters.startdate

@pipeline().parameters.enddate

I'm still getting the same "deserialized error" when trying all that.

Ganesh Gurram 1,510 Reputation points Microsoft Vendor

2024-11-18T16:11:45.1766667+00:00

@Alex Nieto Castro - I agree that this issue looks strange, and I wasn't able to reproduce this issue. If you have a support plan, could you please file a support ticket for deeper investigation and do share the SR# with us?

Alex Nieto Castro 0 Reputation points

2024-11-20T22:22:57.64+00:00

I do have an open ticket with Microsoft, but it does not look promising. The more eyes looking at it the better, right?

Ganesh Gurram 1,510 Reputation points Microsoft Vendor

2024-11-21T02:12:10.69+00:00

@Alex Nieto Castro - Thank you for the update. I understand your frustration, and I agree that having more eyes on the issue could help expedite the resolution. If your open ticket with Microsoft isn't progressing as expected, it might be helpful to follow up with them while we continue to investigate from our side as well. If you could share the SR# with us, we can ensure we're aligned and assist in any way possible.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.