2

I've got a SQL task that creates a temp table, then a data flow task that selects from that temp table. Everything works fine and dandy, but when I add a parameter to the SQL task, the data flow task fails, saying the temp table wasn't created. Hard coding the parameter values works.

The retain same connection option is true, metadata is set, and my Int32 variables are mapped as long with names 0 and 1 and size -1 for oledb.

I've managed to replicate it on a smaller scale

SQL Task

DECLARE @Yesterday DATETIME
DECLARE @Today DATETIME

DECLARE @StartDisposition INT = ?
DECLARE @EndDisposition INT = ?

SET @Yesterday = CONVERT (date, DATEADD(day, @StartDisposition, GETDATE()))
SET @Today = CONVERT (date, DATEADD(day, @EndDisposition, GETDATE()))

SELECT @StartDisposition AS A, @Yesterday AS B, @Today AS C INTO #TempT

Data Flow Task, or just a SQL Task for this purpose

SELECT * FROM #TempT

So something with the parameters seems to be messing up the creation of the temp table.

Thanks

Using a String variable expression for the query worked. Here's how it went.

"DECLARE @Yesterday DATETIME
DECLARE @Today DATETIME

DECLARE @StartDisposition INT = " + (DT_STR, 20, 1252)@[User::StartDisposition] + "
DECLARE @EndDisposition INT =  " + (DT_STR, 20, 1252)@[User::EndDisposition] + "

SET @Yesterday = CONVERT (date, DATEADD(day, @StartDisposition, GETDATE()))
SET @Today = CONVERT (date, DATEADD(day, @EndDisposition, GETDATE()))

SELECT @StartDisposition AS A, @Yesterday AS B, @Today AS C INTO #TempT"

Then in the SQL Task you change the SQLSourceType to Variable

Thanks for your help everyone.

Jooooosh
  • 331
  • 2
  • 4
  • 13
  • I have been wondering the same thing too about TEMP tables with SSIS. I read somewhere they may not work in 2012 for some reason. Hopefully someone gives a solid explanation. – programnub112 Dec 15 '14 at 18:49
  • Yeah hopefully. I'm working in 2010 though. I just tried it with the sql source as a variable, and it seemed to work. I'd rather not resort to that though. – Jooooosh Dec 15 '14 at 18:50
  • I got the same error in 2012, haven't tried 2010. In 2008 temp tables work fine in the procedures executed through data flow tasks. – programnub112 Dec 15 '14 at 18:50
  • 3
    Why load the item into a #table and then have a DFT pull from the temp table? Couldn't you just use the query that populates the #table? Or possibly turn this into a stored procedure? – Zane Dec 15 '14 at 19:11
  • I'm using a column that's in the temp table to grab data from another table. I can't create procedures on the server either. – Jooooosh Dec 15 '14 at 19:15
  • 1
    I remember running into this issue. Instead of using temp I created a table and dropped it when I was done. – SQLChao Dec 15 '14 at 19:23
  • Unfortunately I can't create tables on that server. – Jooooosh Dec 15 '14 at 19:25
  • Putting the query into a variable expression got it to work. Thanks for your help guys. I'll put the expression in my question in case someone finds it useful. – Jooooosh Dec 15 '14 at 19:45

1 Answers1

0

Sometime strange.But It can be solved if we create global temp table ( ##TempT)

Ezhil Arasan
  • 450
  • 3
  • 5