Can you explain why the global temp table is a better option than a permanent staging table? That's not clear from your explanation.

As far as I know, a global temp table remains in scope as long as the SPID which created it remains active. So you are likely to get conflicts if...

• This reply was modified 3 years, 6 months ago by Phil Parkin.
• This reply was modified 3 years, 6 months ago by Phil Parkin. Reason: Typos
• This reply was modified 3 years, 6 months ago by Phil Parkin.

Here's a revised version. Note that the CREATE TABLE and INSERTs should usually be done by the person posting the question – I'm helping you out because because you're new...

Here is the tricky part done. All you need to do is JOIN from the final query to your Ref table to get the 'Month'.

DROP TABLE IF...

Try this:

SELECT VID
      ,OrdDate
  ,CResult = MAX(IIF(QID = 'C Result', Response, NULL))
  ,RAScore = MAX(IIF(QID = 'RA Score', Response, NULL))
...

There is a fundamental problem with this – SSIS is a server-based technology and therefore cannot practically be used interactively, except during development.

Can you also post an image showing the results you would like to see, based on this data?

The following expression returns 20220501 if FileCode = 1, and an empty string otherwise.

@[User::FileCode]==1?token(@[User::FileName], "_", 4) : ""

If the file name is "ImportFile_UK_DumpXX_20220501", what exactly do you want the expression to return?

If FileCode <> 1, what do you want the expression to return?

Yes, I agree that a Foreach loop (FEL) with an object variable sounds like the way to go.

Is there really a direct mapping between the package names and the table...

ON (1 =1) is unusual JOIN syntax – can you explain what you want it to do?

I have some ideas – they involve GROUP BY and MAX(). You have nearly 4,000 points and this is simple stuff. What have you tried so far?

Please complete the following VALUES section code to give us your test data to work with:

DROP TABLE IF EXISTS #SomeTab;

CREATE TABLE #SomeTab
(
    Id INT...

Not very elegant, but I think that this works:

WITH c1
AS (SELECT *
          ,Ctr = IIF(conversion_factor IS NULL, 0,...

Here is that link again: https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help