0

I have a SSIS 2014 package where I am using a SP. That SP is creating the temp table and inserting data into that temp table and returning the result set. When I use the package with SQL SERVER 2005 then it is working fine but when I use the SQL SERVER 2014 database then it is returning the below error:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'INSERT INTO #tblTemp ( id ,username ,indicator' in procedure 'p_GetFullData' uses a temp table.".

I have also SET FMTONLY OFF but it is also not working.

Pankaj Saha
  • 869
  • 3
  • 17
  • 37

2 Answers2

1

Set FMT Only Off no longer works! I was surprised too.

Instead you can trick SSIS by running a Select where 1=0 as the first Select in the stored procedure. Then carry on as normal in your stored procedure.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
john McTighe
  • 1,181
  • 6
  • 8
0

You can try following:

  1. Enable delay validation
  2. Use Table variable instead of Temp table
p2k
  • 2,126
  • 4
  • 23
  • 39