Skip to content

Commit 5f84a4f

Browse files
itechedititechedit
authored
Merge pull request #72082 from ecfan/pagination
Refactor pagination content - publish 4/25
2 parents 112a63e + 94119a1 commit 5f84a4f

File tree

9 files changed

+142
-70
lines changed

9 files changed

+142
-70
lines changed

‎articles/connectors/connectors-create-api-sqlazure.md

+25-22
Original file line numberDiff line numberDiff line change
@@ -171,36 +171,39 @@ From the actions list, select any SQL action that you want.
171171

172172
[!INCLUDE [Create a connection to SQL Server or Azure SQL Database](../../includes/connectors-create-api-sqlazure.md)]
173173

174-
## Process data in bulk
174+
## Handle bulk data
175175

176-
When you work with result sets so large that the connector
177-
doesn't return all the results at the same time,
178-
or you want better control over the size and structure for your result sets,
179-
you can use *pagination*, which helps you manage those results as smaller sets.
176+
Sometimes, you might have to work with result sets so large that the
177+
connector doesn't return all the results at the same time, or you
178+
want better control over the size and structure for your result sets.
179+
Here's some ways that you can handle such large result sets:
180180

181-
[!INCLUDE [Set up pagination for results exceeding default page size](../../includes/connectors-pagination-bulk-data-transfer.md)]
181+
* To help you manage results as smaller sets, turn on *pagination*.
182+
For more information, see
183+
[Get bulk data, records, and items by using pagination](../logic-apps/logic-apps-exceed-default-page-size-with-pagination.md).
182184

183-
### Create a stored procedure
185+
* Create a stored procedure that organizes the results the way you want.
184186

185-
When getting or inserting multiple rows, your logic
186-
app can iterate through these items by using an
187-
[*until loop*](../logic-apps/logic-apps-control-flow-loops.md#until-loop)
188-
within these [limits](../logic-apps/logic-apps-limits-and-config.md).
189-
But, sometimes your logic app has to work with record sets so large,
190-
such as thousands or millions of rows, that you want to minimize the
191-
costs for calls to the database.
187+
When getting or inserting multiple rows, your logic
188+
app can iterate through these rows by using an
189+
[*until loop*](../logic-apps/logic-apps-control-flow-loops.md#until-loop)
190+
within these [limits](../logic-apps/logic-apps-limits-and-config.md).
191+
However, when your logic app has to work with record sets so large,
192+
for example, thousands or millions of rows, that you want to minimize
193+
the costs resulting from calls to the database.
192194

193-
Instead, you can create a <a href="https://docs.microsoft.com/sql/relational-databases/stored-procedures/stored-procedures-database-engine" target="blank">*stored procedure*</a> that runs in your SQL instance and uses
194-
the **SELECT - ORDER BY** statement to organize the results the way you want.
195-
This solution gives you more control over the size and structure of your results.
196-
Your logic app calls the stored procedure by using the SQL Server
197-
connector's **Execute stored procedure** action.
195+
To organize the results in the way that you want, you can create a
196+
[*stored procedure*](https://docs.microsoft.com/sql/relational-databases/stored-procedures/stored-procedures-database-engine)
197+
that runs in your SQL instance and uses the **SELECT - ORDER BY** statement.
198+
This solution gives you more control over the size and structure of your results.
199+
Your logic app calls the stored procedure by using the SQL Server connector's
200+
**Execute stored procedure** action.
198201

199-
For solution details, see these articles:
202+
For solution details, see these articles:
200203

201-
* <a href="https://social.technet.microsoft.com/wiki/contents/articles/40060.sql-pagination-for-bulk-data-transfer-with-logic-apps.aspx" target="_blank">SQL Pagination for bulk data transfer with Logic Apps</a>
204+
* [SQL Pagination for bulk data transfer with Logic Apps](https://social.technet.microsoft.com/wiki/contents/articles/40060.sql-pagination-for-bulk-data-transfer-with-logic-apps.aspx)
202205

203-
* <a href="https://docs.microsoft.com/sql/t-sql/queries/select-order-by-clause-transact-sql" target="_blank">SELECT - ORDER BY Clause</a>
206+
* [SELECT - ORDER BY Clause](https://docs.microsoft.com/sql/t-sql/queries/select-order-by-clause-transact-sql)
204207

205208
## Connector-specific details
206209

‎articles/logic-apps/logic-apps-exceed-default-page-size-with-pagination.md

+114
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,114 @@
1+
---
2+
title: Get more data, items, or records with pagination - Azure Logic Apps
3+
description: Set up pagination to exceed the default page size limit for connector actions in Azure Logic Apps
4+
services: logic-apps
5+
ms.service: logic-apps
6+
ms.suite: integration
7+
author: ecfan
8+
ms.author: estfan
9+
ms.reviewer: klam, LADocs
10+
ms.topic: article
11+
ms.date: 04/11/2019
12+
---
13+
14+
# Get more data, items, or records by using pagination in Azure Logic Apps
15+
16+
When you retrieve data, items, or records by using a connector action in
17+
[Azure Logic Apps](../logic-apps/logic-apps-overview.md), you might get
18+
result sets so large that the action doesn't return all the results at
19+
the same time. With some actions, the number of results might exceed the
20+
connector's default page size. In this case, the action returns only the
21+
first page of results. For example, the default page size for the SQL Server
22+
connector's **Get rows** action is 2048, but might vary based on other settings.
23+
24+
Some actions let you turn on a *pagination* setting so that your logic
25+
app can retrieve more results up to the pagination limit, but return those
26+
results as a single message when the action finishes. When you use pagination,
27+
you must specify a *threshold* value, which is the target number of results you
28+
want the action to return. The action retrieves results until reaching your
29+
specified threshold. When your total number of items is less than the specified
30+
threshold, the action retrieves all the results.
31+
32+
Turning on the pagination setting retrieves pages of results based on a connector's page size.
33+
This behavior means that sometimes, you might get more results than your specified threshold.
34+
For example, when using the SQL Server **Get rows** action, which supports pagination setting:
35+
36+
* The action's default page size is 2048 records per page.
37+
* Suppose you have 10,000 records and specify 5000 records as the minimum.
38+
* Pagination gets pages of records, so to get at least the specified minimum,
39+
the action returns 6144 records (3 pages x 2048 records), not 5000 records.
40+
41+
Here's a list with just some of the connectors where you
42+
can exceed the default page size for specific actions:
43+
44+
* [Azure Blob Storage](https://docs.microsoft.com/connectors/azureblob/)
45+
* [Dynamics 365](https://docs.microsoft.com/connectors/dynamicscrmonline/)
46+
* [Excel](https://docs.microsoft.com/connectors/excel/)
47+
* [HTTP](https://docs.microsoft.com/azure/connectors/connectors-native-http)
48+
* [IBM DB2](https://docs.microsoft.com/connectors/db2/)
49+
* [Microsoft Teams](https://docs.microsoft.com/connectors/teams/)
50+
* [Oracle Database](https://docs.microsoft.com/connectors/oracle/)
51+
* [Salesforce](https://docs.microsoft.com/connectors/salesforce/)
52+
* [SharePoint](https://docs.microsoft.com/connectors/sharepointonline/)
53+
* [SQL Server](https://docs.microsoft.com/connectors/sql/)
54+
55+
## Prerequisites
56+
57+
* An Azure subscription. If you don't have an Azure subscription yet,
58+
[sign up for a free Azure account](https://azure.microsoft.com/free/).
59+
60+
* The logic app and the action where you want to turn on pagination.
61+
If you don't have a logic app, see
62+
[Quickstart: Create your first logic app](../logic-apps/quickstart-create-first-logic-app-workflow.md).
63+
64+
## Turn on pagination
65+
66+
To determine whether an action supports pagination in the Logic App Designer,
67+
check the action's settings for the **Pagination** setting. This example shows
68+
how to turn on pagination in the SQL Server's **Get rows** action.
69+
70+
1. In the action's upper-right corner, choose the
71+
ellipses (**...**) button, and select **Settings**.
72+
73+
![Open the action's settings](./media/logic-apps-exceed-default-page-size-with-pagination/sql-action-settings.png)
74+
75+
If the action supports pagination,
76+
the action shows the **Pagination** setting.
77+
78+
1. Change the **Pagination** setting from **Off** to **On**.
79+
In the **Threshold** property, specify an integer value for
80+
the target number of results that you want the action to return.
81+
82+
![Specify minimum number of results to return](./media/logic-apps-exceed-default-page-size-with-pagination/sql-action-settings-pagination.png)
83+
84+
1. When you're ready, choose **Done**.
85+
86+
## Workflow definition - pagination
87+
88+
When you turn on pagination for an action that supports this capability,
89+
your logic app's workflow definition includes the `"paginationPolicy"`
90+
property along with the `"minimumItemCount"` property in that action's
91+
`"runtimeConfiguration"` property, for example:
92+
93+
```json
94+
"actions": {
95+
"HTTP": {
96+
"inputs": {
97+
"method": "GET",
98+
"uri": "https://www.testuri.com"
99+
},
100+
"runAfter": {},
101+
"runtimeConfiguration": {
102+
"paginationPolicy": {
103+
"minimumItemCount": 1000
104+
}
105+
},
106+
"type": "Http"
107+
}
108+
},
109+
```
110+
111+
## Get support
112+
113+
For questions, visit the
114+
[Azure Logic Apps forum](https://social.msdn.microsoft.com/Forums/en-US/home?forum=azurelogicapps).

‎articles/logic-apps/logic-apps-workflow-actions-triggers.md

+1
Original file line numberDiff line numberDiff line change
@@ -2548,6 +2548,7 @@ properties in the trigger or action definition.
25482548
| `runtimeConfiguration.concurrency.runs` | Integer | Change the [*default limit*](../logic-apps/logic-apps-limits-and-config.md#looping-debatching-limits) on the number of workflow instances that can run at the same time, or in parallel. This value can help limit the number of requests that backend systems receive. <p>Setting the `runs` property to `1` works the same way as setting the `operationOptions` property to `SingleInstance`. You can set either property, but not both. <p>To change the default limit, see [Change trigger concurrency](#change-trigger-concurrency) or [Trigger instances sequentially](#sequential-trigger). | All triggers |
25492549
| `runtimeConfiguration.concurrency.maximumWaitingRuns` | Integer | Change the [*default limit*](../logic-apps/logic-apps-limits-and-config.md#looping-debatching-limits) on the number of workflow instances that can wait to run when your workflow is already running the maximum concurrent instances. You can change the concurrency limit in the `concurrency.runs` property. <p>To change the default limit, see [Change waiting runs limit](#change-waiting-runs). | All triggers |
25502550
| `runtimeConfiguration.concurrency.repetitions` | Integer | Change the [*default limit*](../logic-apps/logic-apps-limits-and-config.md#looping-debatching-limits) on the number of "for each" loop iterations that can run at the same time, or in parallel. <p>Setting the `repetitions` property to `1` works the same way as setting the `operationOptions` property to `SingleInstance`. You can set either property, but not both. <p>To change the default limit, see [Change "for each" concurrency](#change-for-each-concurrency) or [Run "for each" loops sequentially](#sequential-for-each). | Action: <p>[Foreach](#foreach-action) |
2551+
| `runtimeConfiguration.paginationPolicy.minimumItemCount` | Integer | For specific actions that support and have pagination turned on, this value specifies the *minimum* number of results to retrieve. <p>To turn on pagination, see [Get bulk data, items, or results by using pagination](../logic-apps/logic-apps-exceed-default-page-size-with-pagination.md) | Action: Varied |
25512552
|||||
25522553

25532554
<a name="operation-options"></a>

‎articles/logic-apps/media/logic-apps-exceed-default-page-size-with-pagination/sql-action-settings-pagination.png

5.29 KB
Loading

‎articles/logic-apps/media/logic-apps-exceed-default-page-size-with-pagination/sql-action-settings.png

8 KB
Loading

‎articles/logic-apps/toc.yml

+2
Original file line numberDiff line numberDiff line change
@@ -61,6 +61,8 @@
6161
href: logic-apps-scenario-function-sb-trigger.md
6262
- name: Call, trigger, or nest logic apps
6363
href: logic-apps-http-endpoint.md
64+
- name: Get bulk data with pagination
65+
href: logic-apps-exceed-default-page-size-with-pagination.md
6466
- name: Batch process messages
6567
href: logic-apps-batch-process-send-receive-messages.md
6668
- name: Author JSON definitions

‎includes/connectors-pagination-bulk-data-transfer.md

-48
This file was deleted.

‎includes/media/connectors-pagination-bulk-data-transfer/sql-action-settings-pagination.png

-4.81 KB
Binary file not shown.

‎includes/media/connectors-pagination-bulk-data-transfer/sql-action-settings.png

-7.21 KB
Binary file not shown.

0 commit comments

Comments
 (0)