Unfortunately, there is no elegant way to this. This is actually a recent community ask posted to Microsoft as a new feature.
JediSQL points out the difficulty of capturing the...

Here's a method from Pinal Dave that I've found useful:
https://blog.sqlauthority.com/2017/05/25/sql-server-simple-query-list-size-table-row-counts/

Here's reference for User-Defined Table Types:

https://technet.microsoft.com/en-us/library/bb522526%28v=sql.105%29.aspx
https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine

Perhaps this?

USE [test]
go

select
    ComponentId,
    partRefId = MIN(partRefId),
    Name,
    DocumentId,
    SheetRef = MIN(SheetRef),
    LocationId,
    Effectivity,
    EffectivityId,
    DesignatorPrefix,
    Designator,
    DesignatorSuffix
FROM PartRef pr

If you're not too concerned about potential performance hits, you could create a new schema for this staging data within your user database, and create your staging tables there.  Then...

Here's one method:

declare @function_param VARCHAR(100) = '2017-06-08 16:00:00'
declare @result VARCHAR(100)
select @result = CONVERT(varchar(100),DATEADD(minute, -1, @function_param),120)
print @result


Do you rely on SSMS native Intellisense for coding?

I think this is normal.  SSMS makes a separate connection to pull metadata for Intellisense.  I don't think it's able...

Have you reviewed the SSIS catalog deployment?
Are the Environment variables setup and mapped correctly to the deployed SSIS project?  Do they match the server that's properly working?

Can...

Can the 3rd Party Software pull data from a stored procedure, or are you stuck with a view as a source?

Can you give more detail about how you're using Excel to access the data?
Are you looking for separate Excel files for each range? Or one file, with separate worksheets?

You'll need to explain how you want to handle different values for the same column to be returned?

Perhaps concatenate the values into a comma delimited value?
Or encoded...

Thanks Lynn as well. Love having alternatives in the toolbelt.

Awesome, thanks dwain! Very elegant solution. I should be able to transfer this logic to the actual data quite easily.

Thanks for the tip. I've actually recently purchased "Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions" written by Itzik Ben-Gan.

I think the main problem is the data set...

I have same sentiments as Jack. SQL Agent jobs is not designed for your 1-min response time.

If you have any future plans for scaling, this solution won't last you...