Post reply

SSIS 2014 Stored proc using temp table in data flow task error

  • April 21, 2015 at 3:07 am

    #307203

    I created a package in SSIS 2008 where I had a stored procedure as source in a data flow task. This is a complex proc and it uses temp tables. I know that there are issues with using temp tables and I overcame it by forcing using the last option as documented here: https://sites.google.com/site/alledocuments/sql/ssis-and-stored-procedures-using-temp-tables

    However, I am now converting this package to SSIS 2014 and it fails with the error "The metadata could not be determined because statement ... uses a temp table".

    I have tried to using SET FMTONLY OFF as suggested in other posts but this still does not work.

    Apart from rewriting the proc to use table variables rather than temp tables, is there anything else I can try to make this work with SSIS 2014?

    Thanks

    Jeremy

  • April 21, 2015 at 3:25 am

    #1792315

    The temp table 'hack' changed in SSIS 2012. Use EXECUTE WITH RESULT SETS, see here.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • April 21, 2015 at 3:43 am

    #1792321

    Thanks. Works a treat.

    Jeremy

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply