SQL Server 2012 SSIS - Creating temp table issue

1
votes

I have the following tasks in my SQL Server 2012 package:

  1. Execute SQL Task to create a local temp table (I need to use local temp table as I will be firing off same concurrent packages)
  2. Data Flow Task: To export from a SQL Server table into the local temp table created in step 1. I have the RatainSameConnection option = TRUE for data sources and DelayValidation for both tasks to true.

Once I run the package, I get this error:

The metadata could not be determined because statement 
select * from #tmptable uses a temp table.

I researched this a lot but couldn't find a good solution. Any help and sample working SSIS package will be appreciated.

3
sql-serverssis
Do you have delayvalidation on the data connection as well? - Greg the Incredulous
Does it have to be temp? can it be permanent? Do you expect multiple sessions to be using it simultaneously (i.e. your package running in parallel in multiple jobs) - Nick.McDermaid
I can't use permanent tables as simultaneous running packages will mess up the data. - Abiete
Try to use a permanent table that gets created and dropped with each execution and includes some aspect of a timestamp with the table name. Instead of dbo.temptable, make it dbo.temptable2014121001210000 or something similar that gives you the ability to create a completely unique name for the moment the package gets executed. You will still need to delay metadata validation, but you won't have the connection issue. Also, if the package fails, you'll have a table to investigate and triage. - Vinnie
Possible duplicate of SSIS Package not wanting to fetch metadata of temporary table - Kyle Hale

3 Answers

2
votes

I just had this same issue and found the solution is to create a fake dataset in the query before you actual SELECT statement that SELECTs static data that is in the same format.

SET FMTONLY ON
select 0 as a, 1 as b, 'test' as C, GETDATE() as D
SET FMTONLY OFF

--now put the real query
select a, b, c, d from ##TempTable

See Kyle Hale's answer - SSIS Package not wanting to fetch metadata of temporary table

1
votes

Go to properties and set ValidateExternalMetadata to False so it won't try to check the metadata.

If that doesn't solve the problem then,

Use global temporary table(##someTable) instead of local temp table(#someTable). so you can create that global temp table using SSMS and then when you use it in SSIS it won't complain about metadata.

Update:

If you want to use local temp table then do this after you've followed the above steps,

From SSIS menu in BIDS, select Work Offline mode and then change your Global temp table to Local temp Table. make sure do it from Property window or better use variable for holding sqlCommand text otherwise SSIS will complain about external metadata again.

0
votes

Generally I replace the temp table with a CTE or table variable.