A more primitive alternative to the above replies. 🙂

declare @table table (
    Number int,
    Date1 date,
    Date2 date,
...

A simple typo I think. If you have just used copy/paste from Ed B's suggested code, the CAST needs a starting paranteses, i.e. CAST(NULL AS VARCHAR(100))

This is just a feeling, but I have had the same problem a time or two. Turned out that different default settings of ARITHABORT between SSMS and the application resulted...

Or maybe a combination of the proposed queries? Something like

WITH LowestHighest AS (

SELECT MAX (vacationhours) as 'Max_VacationHours', MIN (vacationhours) as 'Min_VacationHours'

From [HumanResources].[Employee]

)

SELECT 'Lowest' AS VacationHoursLeft, e.*, lh.Min_VacationHours AS VacationHours

FROM [HumanResources].[Employee]...

Was the WHERE clause allowed when the COLUMNSTORE index was first introduced with SQL Server 2012? I think not, but I haven't been able to find the official MS documentation...

• This reply was modified 3 years, 2 months ago by kaj.

Alternatively you'll need to use a window function instead of a conventional group by.

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 3 years, 3 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 3 years, 3 months ago by kaj.

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