0

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
TomLenzmeier
  • 49
  • 1
  • 10
  • 1
    Maybe you can shorten the query while still having the same problem. This query is not easy to read. It would help a lot if you would use some indentation and white lines – GuidoG Mar 25 '22 at 14:01
  • _When_ do you query for the global temp table? Has the package completed execution or have you set a break point after the Execute SQL Task? – billinkc Mar 25 '22 at 19:35
  • The query above is run as a SQL task. The DFT runs next, with retain connection property set to true, as well as delay validation. Delay validation is also set to true on the DFT. – TomLenzmeier Mar 26 '22 at 23:47
  • in ssis you are not allowed to insert data into temp table. you will need to create a pre defined table. – Atibur Rahman Mar 31 '22 at 04:20

0 Answers0