I suspect that you'll need to upgrade your VS version to match or exceed the SQL Server version (ie, 2019 or 2022).

thanks Phil! I appreciate the quick response, please understand if I'm asking on a forum it's because I have tried to resolve it first and then subsequently trolled through...

SELECT ...
FROM ...
WHERE DATEADD(WEEK, 4, appl_CreatedDate) <= appl_ConversionDate;

You've stated a requirement without asking a question.

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, 4 months ago by Phil Parkin.
• This reply was modified 3 years, 4 months ago by Phil Parkin. Reason: Typos
• This reply was modified 3 years, 4 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?