It would have been nice if you had provided test data in a format that is readily usable. That's generally a prerequisite for having any success in getting a response...

I'd say you have far too many single-quotes. Each single-quote in your original query only needs to be converted into two, in order to be represented in a variable. You...

A different approach that may be possible is to feed you table source into a Destination Script Component. In this you can define more than one downstream named output destinations....

• This reply was modified 1 year, 11 months ago by  kaj.

I also think you'll have to do the limiting of the output data size at the source, i.e. use a SELECT TOP(calculatedNumberOfRows) * FROM yourTable. The number of rows that...

Looks like it's referencing SQL Agent tokens.

https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps?redirectedfrom=MSDN&view=sql-server-ver15

Alternatively you can define five (or the number of packages you have) separate Data Flows, where your input will then be slightly modified queries:

"Select tablenumber from a table where tablenumber=1",...

As an alternative this could possibly/probably be better handled by SSIS than what you describe as your current solution.

Instead of writing to a temp table, just pass the data to...

It is the same as the Report Builder. SSRS is a Server Service. The Power...

• This reply was modified 1 year, 11 months ago by  kaj.

What are the use cases for this product vs the regular SSRS reporting product?

At a guess you have one too many single quotes (apostrophes) at the end of this line, i.e. four where it should have been only three:

SET @sqlz =...

This query will only work if there is exactly one part.  I added one more record for a different part, and now I am getting no records at all.

Instead of using the apply approach you can also try a reworked query, which works wth the minimal test data you provided:

declare @v_po_history table (
  ...

• This reply was modified 1 year, 11 months ago by  kaj. Reason: TYPO: prived --> provided

Okay, since we now have learned that you're using Pervasive SQL (BTrieve based) that changes things.

I don't know anything about...

I fully agree with the comments made by others, but if the OP would like to try anyway, this will work with the test data provided:

 select...

Something like this perhaps?

declare @v_po_history table (
    Part varchar(20),
    Lowest_Cost dec(9,6),
    Date_Received date
    )

insert into...