1

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.

Dnyanesh
  • 2,265
  • 3
  • 20
  • 17
Abiete
  • 105
  • 2
  • 10
  • Do you have delayvalidation on the data connection as well? – Greg the Incredulous Dec 03 '14 at 01:52
  • 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.Mc Dec 03 '14 at 03:27
  • I can't use permanent tables as simultaneous running packages will mess up the data. – Abiete Dec 09 '14 at 23:17
  • 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 Dec 10 '14 at 06:24
  • Possible duplicate of [SSIS Package not wanting to fetch metadata of temporary table](http://stackoverflow.com/questions/18346484/ssis-package-not-wanting-to-fetch-metadata-of-temporary-table) – Kyle Hale May 02 '16 at 21:58

3 Answers3

2

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

Community
  • 1
  • 1
Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
1

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.

gunvant.k
  • 1,096
  • 1
  • 10
  • 15
  • I changed ValidateExternalMetadatabut it doesn't help. As I mentioned in the original post, I can't use global temp table due to concurrency of SSIS package run. – Abiete Dec 10 '14 at 00:58
0

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

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
HLGEM
  • 94,695
  • 15
  • 113
  • 186