Truncate a Dataverse table from ADF

Question

Truncate a Dataverse table from ADF

Saurav Panda [C] 5

Hi Team, I am currently work with ADF where i am loading data to dataverse from multiple source. My stage table is also in dataverse so before loading i want to truncate the table. Please help me

Harishga 6,005 Reputation points Microsoft External Staff

2024-01-30T08:19:59.6433333+00:00
Hi @Saurav Panda [C]
Welcome to Microsoft Q&A platform and thanks for posting your question here.

To truncate a table in Dataverse using Azure Data Factory before loading data from multiple sources, you can use the pre-copy script feature in Azure Data Factory. This feature allows you to execute a script before the copy activity starts. Here are the steps to follow:

Open the Sink tab for your copy activity.

Populate the Pre copy script field with the following code snippet:

TRUNCATE TABLE <table_name>

where <table_name> is the name of the table you want to truncate.

You can also use the lookup activity to get the list of tables to truncate. Here are the steps to follow:

Get the list of tables to truncate comma-separated using lookup/parameter, etc.

Create a truncate query by replacing , with ;:

TRUNCATE TABLE <table_name1>; TRUNCATE TABLE <table_name2>; TRUNCATE TABLE <table_name3>; ...

where <table_name1>, <table_name2>, <table_name3>, etc. are the names of the tables you want to truncate.

I hope this helps! Let me know if you have any other questions.
Harishga 6,005 Reputation points Microsoft External Staff

2024-01-31T06:29:18.2766667+00:00

Hi @Saurav Panda [C]
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.
Saurav Panda [C] 5 Reputation points

2024-01-31T20:08:50.6966667+00:00

This option works for SQL database. when we add dataverse in sink dataset the Pre-SQL option is missing.
Harishga 6,005 Reputation points Microsoft External Staff

2024-02-01T11:03:09.8866667+00:00
Hi @Saurav Panda [C]

In this case, you can use the Web API destination in Azure Data Factory to truncate the table in Dataverse before loading data from multiple sources.

Here are the steps to follow:

Create a Web API destination in your Azure Data Factory pipeline.

In the Web API destination settings, set the HTTP method to POST.

Set the URL to the following format, replacing <organization> and <table_name> with your organization name and the name of the table you want to truncate:

https://<organization>.crm.dynamics.com/api/data/v9.1/truncate/<table_name>

In the Headers section, add the following key-value pairs:

Content-Type: application/json OData-MaxVersion: 4.0 OData-Version: 4.0 Accept: application/json Prefer: return=representation

In the Body section, add an empty JSON object:

{}

Save and run your pipeline.

I hope this helps! Let me know if you have any other questions.
Saurav Panda [C] 5 Reputation points

2024-02-02T20:40:40.2166667+00:00

Hi @Harishga Thanks a lot for helping out but the activity failed. Could you please to help me understand on my mistake.

Error The pipeline run failed due to an activity failure. Please check the activity grid below for more detailed information.

Invoking endpoint failed with HttpStatusCode - '401 : Unauthorized', message - 'Client request has not been completed because it lacks valid authentication credentials for the requested endpoint(url).'

So, I added client id and secret key but not sure what needs to be added for "Resource"
Harishga 6,005 Reputation points Microsoft External Staff

2024-02-05T07:50:36.56+00:00
Hi @Saurav Panda [C]
It looks like you are getting a 401 Unauthorized error when invoking the endpoint in your Web API destination. This error occurs when the client request lacks valid authentication credentials for the requested endpoint URL. To resolve this issue, you need to provide the correct authentication credentials in your Web API destination settings. Here are the steps to follow:

In the Web API destination settings, go to the Authentication tabSelect OAuth 2.0 as the authentication type.

In the Client ID field, enter the client ID of the Azure AD application that has the necessary permissions to access your Dataverse instance.

In the Client Secret field, enter the client secret of the Azure AD application.. In the Tenant ID field, enter the ID of your Azure AD tenant.

In the Resource field, enter the following value: https://<organization>.crm.dynamics.com, replacing <organization> with your organization name.

After providing the correct authentication credentials, save your Web API destination settings and run your pipeline again. This should resolve the 401 Unauthorized error and allow you to truncate the table in Dataverse before loading data from multiple sources.

Let me know if you have any other questions.
Madrane Soufiane 0 Reputation points

2024-02-07T10:34:35.6066667+00:00

Hi, I tried this solution but it seems truncate is not supporter. I got this message:
{ "errorCode": "2108", "message": "{"error":{"code":"0x80060888","message":"Resource not found for the segment 'truncate'."}}", "failureType": "UserError", "target": "test1", "details": []

Your answer

Harishga 6,005 Reputation points Microsoft External Staff

2024-01-30T08:19:59.6433333+00:00

Hi @Saurav Panda [C]
Welcome to Microsoft Q&A platform and thanks for posting your question here.

To truncate a table in Dataverse using Azure Data Factory before loading data from multiple sources, you can use the pre-copy script feature in Azure Data Factory. This feature allows you to execute a script before the copy activity starts. Here are the steps to follow:

Open the Sink tab for your copy activity.

Populate the Pre copy script field with the following code snippet:

TRUNCATE TABLE <table_name>

where <table_name> is the name of the table you want to truncate.

You can also use the lookup activity to get the list of tables to truncate. Here are the steps to follow:

Get the list of tables to truncate comma-separated using lookup/parameter, etc.

Create a truncate query by replacing , with ;:

TRUNCATE TABLE <table_name1>; TRUNCATE TABLE <table_name2>; TRUNCATE TABLE <table_name3>; ...

where <table_name1>, <table_name2>, <table_name3>, etc. are the names of the tables you want to truncate.

I hope this helps! Let me know if you have any other questions.
Harishga 6,005 Reputation points Microsoft External Staff

2024-01-31T06:29:18.2766667+00:00

Hi @Saurav Panda [C]
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.
Saurav Panda [C] 5 Reputation points

2024-01-31T20:08:50.6966667+00:00

This option works for SQL database. when we add dataverse in sink dataset the Pre-SQL option is missing.
Harishga 6,005 Reputation points Microsoft External Staff

2024-02-01T11:03:09.8866667+00:00

Hi @Saurav Panda [C]

In this case, you can use the Web API destination in Azure Data Factory to truncate the table in Dataverse before loading data from multiple sources.

Here are the steps to follow:

Create a Web API destination in your Azure Data Factory pipeline.

In the Web API destination settings, set the HTTP method to POST.

Set the URL to the following format, replacing <organization> and <table_name> with your organization name and the name of the table you want to truncate:

https://<organization>.crm.dynamics.com/api/data/v9.1/truncate/<table_name>

In the Headers section, add the following key-value pairs:

Content-Type: application/json OData-MaxVersion: 4.0 OData-Version: 4.0 Accept: application/json Prefer: return=representation

In the Body section, add an empty JSON object:

{}

Save and run your pipeline.

I hope this helps! Let me know if you have any other questions.
Saurav Panda [C] 5 Reputation points

2024-02-02T20:40:40.2166667+00:00

Hi @Harishga Thanks a lot for helping out but the activity failed. Could you please to help me understand on my mistake.

Error The pipeline run failed due to an activity failure. Please check the activity grid below for more detailed information.

Invoking endpoint failed with HttpStatusCode - '401 : Unauthorized', message - 'Client request has not been completed because it lacks valid authentication credentials for the requested endpoint(url).'

So, I added client id and secret key but not sure what needs to be added for "Resource"
Harishga 6,005 Reputation points Microsoft External Staff

2024-02-05T07:50:36.56+00:00

Hi @Saurav Panda [C]
It looks like you are getting a 401 Unauthorized error when invoking the endpoint in your Web API destination. This error occurs when the client request lacks valid authentication credentials for the requested endpoint URL. To resolve this issue, you need to provide the correct authentication credentials in your Web API destination settings. Here are the steps to follow:

In the Web API destination settings, go to the Authentication tabSelect OAuth 2.0 as the authentication type.

In the Client ID field, enter the client ID of the Azure AD application that has the necessary permissions to access your Dataverse instance.

In the Client Secret field, enter the client secret of the Azure AD application.. In the Tenant ID field, enter the ID of your Azure AD tenant.

In the Resource field, enter the following value: https://<organization>.crm.dynamics.com, replacing <organization> with your organization name.

After providing the correct authentication credentials, save your Web API destination settings and run your pipeline again. This should resolve the 401 Unauthorized error and allow you to truncate the table in Dataverse before loading data from multiple sources.

Let me know if you have any other questions.
Madrane Soufiane 0 Reputation points

2024-02-07T10:34:35.6066667+00:00

Hi, I tried this solution but it seems truncate is not supporter. I got this message:
{ "errorCode": "2108", "message": "{"error":{"code":"0x80060888","message":"Resource not found for the segment 'truncate'."}}", "failureType": "UserError", "target": "test1", "details": []

Share via

Truncate a Dataverse table from ADF

Your answer