Dynamic SQL Query To Temp Table In SSIS

Question

I am attempting to create a global temp table based off of a dynamic SQL query. I am using an execute SQL task. Thus far, whenever I execute it, the temp table is not created. Below is my script. I have altered my connection property so that it retains the connection and I set the delay validation to true in the data flow task. When I query for the global temp table, the object doesn't exist.

        drop table if exists ##HOCExp

    DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
    SET @columns = N'';
    SELECT  @columns += N', p.' + QUOTENAME(ShortLabel)
    FROM (SELECT p.ShortLabel FROM dbo.TECHEnumValue AS p
    JOIN dbo.CNTHospitalOrClinicExposure AS o
    ON p.EnumValue = o.MPLEntityExposureBasis
    where p.MyEnumType_ID = 610 and p.ShortLabel <>''
    GROUP BY p.ShortLabel) AS x;
    SET @sql = N'
    SELECT EntityFK, ' + STUFF(@columns, 1, 2, '') + '
    into ##HOCExp
    FROM
    (
    SELECT p.ShortLabel
    , o.MPLEntityExposureUnit
    , concat(o.MyInsuredItemHospitalOrClinic_ClassID
    , o.MyInsuredItemHospitalOrClinic_ID) as EntityFK
    FROM dbo.TECHEnumValue AS p
    INNER JOIN dbo.CNTHospitalOrClinicExposure AS o ON p.EnumValue = o.MPLEntityExposureBasis
    where p.EnumValue <>0 and p.MyEnumType_ID = 610 and o.MyInsuredItemHospitalOrClinic_ID <>0
    ) AS j
    PIVOT
    (
    SUM(MPLEntityExposureUnit) FOR ShortLabel IN ('
    + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
    + ')
    ) AS p;';
    --PRINT @sql;
    EXEC sp_executesql @sql;
    select * from ##HOCExp