SSIS SQL Task with parameters not creating temp table

Question

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.

Answer 1

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

Answer 2

I know this is an old post, but you can work around by creating the temp separately in a SQL Task before your task to load the table. Doing the SELECT INTO #temp with parameter seems to be issue but when doing INSERT #temp with parameter works.