Issue while using the temp table in the SP with SSIS 2014

0
votes

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.

2
sql-serverssissql-server-2014sql-server-data-toolssql-server-2012-datatools

2 Answers

1
votes

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.

0
votes

You can try following:

  1. Enable delay validation
  2. Use Table variable instead of Temp table