CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlssis
JsonStatham
JsonStatham

Reputation: 10364

SSIS job including SQL which creates temporary table

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

Upvotes: 0

Views: 1074

Answers (3)

Will Wainwright
Will Wainwright

Reputation: 332

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/

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

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

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.

Upvotes: 1

Diego
Diego

Reputation: 36146

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.

Upvotes: 1

Related Questions