Still can't get pass this error:

Type conversion in expression (CONVERT(int,[d2].[attValue],0)) may affect "CardinalityEstimate" in query plan choice, The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant...

I second everything Brian Gale has posted here. Excellent advice.

Another possibility is pushing the contents of the table to a text file on the remote server, zipping that, copy to local server, unzip and import.

As implied in the previous...

Perhaps try modifying your Master package to spawn only four copies of GetNextTable?

Given that it bombed out at 4GB before, it's a bit strange how it only made it to...

As the package is held in encrypted form in SSISDB, I expect this to be quite difficult to achieve.

I'm not quite understanding the sequence of events.

Does the code you have...

OK, maybe this will help. Try adding the following 5 lines of code outside and after the first Using block:

...
       ...

I don't know how to fix things using your current top-down package hierarchy, though I do know that there used to be a memory leak in FOR loops years ago...

OUTER APPLY is the way to go, but you need to also modify the initial SELECT

SELECT ISNULL(ca1.Extension, a.Extension)
      ,ISNULL(ca1.Name, a.Name)
  ...

When you refer to 'spawning' of packages, is this done via multiple Execute Package tasks, or in some other way?

--Removed

• This reply was modified 4 years ago by Phil Parkin.

You've been here long enough to know that your sample data should be provided in a consumable format, for us to cut & paste into SSMS.

Where does 'Extension' 1 come...

• This reply was modified 4 years ago by Phil Parkin.

Please try using my modified query and see whether it gives the results you are hoping for:

WITH AffectedVehicles
AS (SELECT DISTINCT
       ...

I should get a chance to look at this again tomorrow regarding the additional engine requirement.

Didn't take long, maybe 30 minutes in total for both solutions.

Here are two duplicate rows: (x,y,z), (x,y,z)

Now tell me what your 'index column' (whatever...

• This reply was modified 4 years ago by Phil Parkin.

I had a quick go at this, see whether it helps.

DECLARE @Mechanic VARCHAR(4) = 'Mike';
DECLARE @MessedUpStart DATETIME = '2000-01-03 14:00:00';
DECLARE @MessedUpEnd DATETIME = '2000-01-03 16:00:00';

DROP TABLE IF...