SSIS and Stored procedures using temp tables

  • Thanks, Michael. I had to apply your workaround today. I've got a parameterized stored procedure returning hundreds of thousands of rows. It needs a temporary table for performance reasons; table variables just don't cut it, due to lack of indexes. For the same reason, executing this procedure 5 times isn't very appealing either. So the 1=2 workaround is perfect. SSIS doesn't appear to know the difference, so long as the fields and datatypes are identical. Awesome!

  • Hi,

    Thanks for this good article. it really helpful for newcomer in ssis. I was facing problem with columns when I was trying to call stored procedure in ssis.

    once againg thank you.

    regards,

    mayur

  • Thanks for the research. This helps me to avoid having to create and drop a physical table in the database to provide the "contract" for DTSPipeline.

    I prefer to using a table variable for the output of procedure. For me the first thing to do in this sort of stored procedure is to declare table a table viarable for output following by your "Wacky" solution. After this setup, you can still use temp tables in your procedure.

    Declare @tblOutput Table (

    ...

    )

    If 1=2

    Begin

    Select * from @tblOutput

    End

  • Micheal,

    I know you wrote this article a while ago but I wanted to say "thanks". Today is my first "real" day with SSIS and it's nice to see that SSIS has some "whacky" features like T-SQL does. Neat stuff and a nicely written article. Thanks!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for this nice article, it solve my problem !

    I used variable tables in place of temp tables => do not forget "SET NOCOUNT ON" at the beginning of the stored procedure.

    Thanks again !!!!

  • EXCELLENT ARTICLE !!!!!

    Saved my day.

    Thank Michael Cape

    http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/65112/

  • The answer (in SQL 2012) is to use the 'WITH RESULT SETS' option on the EXEC statement.

    See http://technet.microsoft.com/en-us/library/ms188332.aspx for specifics.

    Rob Schripsema
    Propack, Inc.

  • http://technet.microsoft.com/en-us/library/ms188332.aspx

    Rob Schripsema
    Propack, Inc.

  • This worked for me. Ideally, I would use the "Results Set" method but I have 2 environments on differing versions of SQL Server. This saved me much work today. Thank you!

Viewing 9 posts - 16 through 23 (of 23 total)

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