SSIS and temp tables

  • hi folks

    i have a stored procedure that i want to run within an SSIS data flow package (sql 2016, vs community 2019)

    the ole db source is the sp, and the sp creates 3 temp tables as well as lot of other standard sql lookups

    so i am getting what seems to be the standard error regarding not being able to read the meta data of the temp tables

    i have changed the connection to delay validation, set the ole source to NOT validate external metadata,

    added SET FMTONLY OFF; to the start of my SP, changed my temp tables to global temp tables

    but i still get the error? only when i create the temp tables in ssms then open the source in ssis does the sp run, but as soon as i drop the tables and run it again from SSIS it fails again

     

    any ideas?

    i am not concerned about the performance hit of SET FMTONLY OFF; as this is will not be run often

     

    thanks

     

    mal

  • All the SET FMTONLY stuff is no longer required and can be removed.

    Instead of

    EXEC proc

    Use

    EXEC proc WITH RESULT SETS ((col1 datatype1, col2 datatype2, ...));

    as the source for your data flow. The RESULT SETS bit describes the expected columns and datatypes which the proc will return – SSIS uses this.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • hi Phil

     

    yes this works perfectly

    i had quite a few columns so i basically wrote the result set into a table, scripted the table as a Create table and tagged the fields onto my sp

     

    great stuff!!

     

    mal

  • Nice one! Thanks for posting back.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • You can do this with older versions of SQL Server too ( I have to use SQL Server 2008, doesn't support WITH RESULT SETS), but you have to do a query (that returns no rows) before you call your proc, to fake up the metadata for the OLEDDB connection. so something like:

    IF 1 = 2
    BEGIN
    SELECT
    -- query that returns data in shape of proc, but no rows returned
    CAST(NULL AS VARCHAR(30)) AS [someval]
    END;

    EXECUTE [dbo].[someproc]

    It's weird because it seems like I don't always need to do this, but when I get that dumb error this'll take care of it. I don't remember where I saw this, but thanks to whomever found this first!

Viewing 5 posts - 1 through 5 (of 5 total)

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