ROW_NUMBER() seems to work:

Paging with ROW_NUMBER()

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
,OrderID
,OrderDate
,CustomerID
,EmployeeID
FROM dbo.Orders
)

SELECT *
FROM OrdersRN
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
ORDER BY OrderDate,OrderID;

I'm sorry I should rephrase my question.  I need to retrieve around 6,000 records that are unique identifiers for our customers.  How can I loop through to get all 6,000 records? 

‌ so if I understood you correctly, what you need is not to loop through the records using Execute SQL action, but you want to get them all at once and the iterate through the collection? If so, then just save output of the Execute SQL into a collection and then do "For Each" to iterate through it.

Regards,

Tomasz

That is correct but how can I retrieve all records if the max results a execute sql function will return is 200?  That's where I am getting stuck. Thanks for your response!!!!!

Do it in a loop then, using suggestions from Fernando and the "Row_Number" - in each loop get next 200 rows, then evaluate if query returned anything, in case yes - process them and then increase the "PageNum" variable. PageSize in that case is 200.

Regards,

Tomasz

I get the logic and have it working in SMSS but haw can I pass the increased variable to a variable inside my execute SQL statement?