SSIS job including SQL which creates temporary table

Question

Have constructed a query which basically looks at a table which contains all bank holidays and then looks at each month for current financial year, then tells me how many working days are available to work minus the bank hols and weekends. For example this month there are 21. There is also a cumulative filed which basically adds up each month so the cumulative for April-Feb will have all those days added.

Within the query there is a CreateTable #DATA which is dropped at the end, this works fine within Management Studio and runs correctly.

My problem is that I am doing an SSIS job and have saved my query as a SQL file and have selected it using the 'Browse' button. It is not allowing me to continue as I believe it has a problem with the temporary table (See screenshot)

Any suggestions on how I can get this to work while maintaining functionality?

Please see code for reference:

DECLARE @StartDate DATETIME, 
        @EndDate   DATETIME 



SELECT @StartDate = (select
case when month(getdate()) >= 4 then
convert(datetime, cast(year(getdate()) as varchar) + '-4-1')
else
convert(datetime, cast(year(getdate())-1 as varchar) + '-4-1')
end), 



       @EndDate = (select
case when month(getdate()) < 4 then
convert(datetime, cast(year(getdate()) as varchar) + '-3-31')
else
convert(datetime, cast(year(getdate())+1 as varchar) + '-3-31')
end) 

CREATE TABLE #data 
  ( 
     firstday    DATETIME NOT NULL PRIMARY KEY, 
     workingdays INT NOT NULL 
  ); 

WITH dayscte ([Date]) 
     AS (SELECT @StartDate 
         UNION ALL 
         SELECT Dateadd(DAY, 1, [Date]) 
         FROM   dayscte 
         WHERE  [Date] <= @Enddate) 
INSERT INTO #data 
SELECT MIN([Date]), 
       COUNT(*) [Day] 


FROM   table2 
       LEFT JOIN [dbo].[mydb].[mytable1]
         ON [Date] BETWEEN [dbo].[mydb].[mytable1].startdate AND [dbo].[mydb].[mytable1].enddate 



where
NOT EXISTS (
    SELECT field1,field2 FROM [dbo].[mydb].[mytable1].tscheme_cal WHERE 
    dayid ='0234572347854234'
    AND
    [date] <= startdate  
    AND
    [date] >= enddate  
    )
       AND Datename(weekday, [Date]) NOT IN ( 'Saturday', 'Sunday' ) 
GROUP  BY Datepart(MONTH, [Date]), 
          Datepart(YEAR, [Date]) 
OPTION (MAXRECURSION 366) 

DECLARE @Date DATETIME 

SET @Date = (SELECT MIN(firstday) 
             FROM   #data) 

SELECT Period, 
       workingdays [Days_Available] ,

       year (firstday) AS [Year]


FROM   (SELECT Datename(MONTH, firstday) [Period], 
               workingdays, 
               0                         [SortField], 
               firstday 
        FROM   #data 
        UNION 
        SELECT Datename(MONTH, @Date) + ' - ' + Datename(MONTH, firstday), 
               (SELECT SUM(workingdays) 
                FROM   #data b 
                WHERE  b.firstday <= a.firstday) [WorkingDays], 
               1                                 [SortField], 
               firstday 
        FROM   #data a 
        WHERE  


        firstday > @Date) data 





ORDER  BY sortfield, 
          firstday 

DROP TABLE #data

enter image description here

Also check out my answer to this similar question http://stackoverflow.com/questions/18346484/ssis-package-not-wanting-to-fetch-metadata-of-temporary-table/18360265#18360265 — Kyle Hale, Dec 03 '14 at 21:28

Diego · Answer 1 · 2012-02-29T14:01:52.333

1

its not easy dealing with temp tables on SSIS. I suggest this article:

http://www.sqllike.com/using-temporary-tables-with-ssis.html

it is a solution, but I don't like it. I sometimes i use table variables or create a regular table on the DB and then drop it in the end.

edited Feb 29 '12 at 14:01

answered Feb 29 '12 at 12:01

Diego

34,802
21
91
134

score 1 · Answer 2 · answered Feb 29 '12 at 14:38

1

In SSIS I find that table variables work well. You can't use temp tables even in a stored proc if it is the source code for a transformation.

answered Feb 29 '12 at 14:38

HLGEM

94,695
15
113
186

You can as long as you declare a dummy result set before your actual query. See also : http://stackoverflow.com/questions/18346484/ssis-package-not-wanting-to-fetch-metadata-of-temporary-table/18360265#18360265 – Kyle Hale Dec 03 '14 at 21:28

score 0 · Answer 3 · edited Jul 13 '15 at 12:22

In SQL Server 2012 if you use temporary tables you must specify a results set.

This is an issue with the sp_describe_first_result_set procedure that SSIS uses to returns the output metadata.

E.g.

EXEC dbo.RptResults_StoredProcedure

Becomes

EXEC dbo.RptResults_StoredProcedure
WITH RESULT SETS
((
    Date NVARCHAR(10),
    Location VARCHAR(12),
    Department CHAR(1),
    Shift CHAR(1),
    ForecastSales DECIMAL(18,2),
    ActualSales DECIMAL(18,2)
))

For more information view

http://blog.concentra.co.uk/2014/08/22/column-metadata-determined-correctly-ssis-data-flow-task-stored-procedure-inputs/

SSIS job including SQL which creates temporary table

3 Answers3