I have dataflow where rows are moved from OLEDB source to OLEDB destination
by bulks defined in dynamically generated select statement within source.
SELECT TOP @batch_size Col1, Col2, Col3
FROM Table1
ORDER BY Col1

The requirement is to save the Col1 value of the last row. Theonly solution
I found in Internet is to direct dataflow to Recordset destination, and then
in ControlFlow continue to ForEachLoop over the recordset, using mapped
variable. The biggest concern here is a preformance.

Does anybody have any experience with similar king of task?
Any suggestions/ideas are much appreciated.


Thanks,
Yuriy

Hello Yuriy:

Seems to me you can get this value using an Execute SQL Task on your Control
Flow:
SELECT MAX(Col1) FROM
(SELECT TOP @batch_Size) Col1
FROM Table1
ORDER BY Col1) AS x

Set the Result Set of the task to single row, and in the ResultSet page, add
a mapping, named 0 to a new package varialbe.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Yuriy Al" wrote:

Hello Yuriy:

Seems to me you can get this value using an Execute SQL Task on your Control
Flow:
SELECT MAX(Col1) FROM
(SELECT TOP @batch_Size) Col1
FROM Table1
ORDER BY Col1) AS x

Set the Result Set of the task to single row, and in the ResultSet page, add
a mapping, named 0 to a new package varialbe.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Yuriy Al" wrote:

Hello Yuriy:

Seems to me you can get this value using an Execute SQL Task on your Control
Flow:
SELECT MAX(Col1) FROM
(SELECT TOP @batch_Size) Col1
FROM Table1
ORDER BY Col1) AS x

Set the Result Set of the task to single row, and in the ResultSet page, add
a mapping, named 0 to a new package varialbe.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Yuriy Al" wrote:

Hello Yuriy:

Seems to me you can get this value using an Execute SQL Task on your Control
Flow:
SELECT MAX(Col1) FROM
(SELECT TOP @batch_Size) Col1
FROM Table1
ORDER BY Col1) AS x

Set the Result Set of the task to single row, and in the ResultSet page, add
a mapping, named 0 to a new package varialbe.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Yuriy Al" wrote:

Hello Yuriy:

Seems to me you can get this value using an Execute SQL Task on your Control
Flow:
SELECT MAX(Col1) FROM
(SELECT TOP @batch_Size) Col1
FROM Table1
ORDER BY Col1) AS x

Set the Result Set of the task to single row, and in the ResultSet page, add
a mapping, named 0 to a new package varialbe.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Yuriy Al" wrote:

Hello Yuriy:

Seems to me you can get this value using an Execute SQL Task on your Control
Flow:
SELECT MAX(Col1) FROM
(SELECT TOP @batch_Size) Col1
FROM Table1
ORDER BY Col1) AS x

Set the Result Set of the task to single row, and in the ResultSet page, add
a mapping, named 0 to a new package varialbe.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Yuriy Al" wrote:

Hello Yuriy:

Seems to me you can get this value using an Execute SQL Task on your Control
Flow:
SELECT MAX(Col1) FROM
(SELECT TOP @batch_Size) Col1
FROM Table1
ORDER BY Col1) AS x

Set the Result Set of the task to single row, and in the ResultSet page, add
a mapping, named 0 to a new package varialbe.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Yuriy Al" wrote:

Hello Yuriy:

Seems to me you can get this value using an Execute SQL Task on your Control
Flow:
SELECT MAX(Col1) FROM
(SELECT TOP @batch_Size) Col1
FROM Table1
ORDER BY Col1) AS x

Set the Result Set of the task to single row, and in the ResultSet page, add
a mapping, named 0 to a new package varialbe.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Yuriy Al" wrote:

Thank you for the answer.
Yes, it is possible, but it will require SQL task to connect to the
destination and read the table. I am trying to avoid this as in large
environment it has performance impact. It is better to read value within
dataflow, as rows are already loaded from the table.

Thanks,
Yuriy



"Todd C" wrote: