SSIS and Stored procedures using temp tables

Question

SSIS and Stored procedures using temp tables

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

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

Brian Pond Valued Member Points: 61 More actions · Answer 1

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!

mayurshendge1 SSC Journeyman Points: 84 More actions · Answer 2

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

dujunbruce Grasshopper Points: 17 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

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)

dindonsquat SSC Rookie Points: 37 More actions · Answer 5

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 !!!!

tonycompu SSC Rookie Points: 43 More actions · Answer 6

EXCELLENT ARTICLE !!!!!

Saved my day.

Thank Michael Cape

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

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 7

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.

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 8

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

Rob Schripsema
Propack, Inc.

omegarazor Newbie Points: 1 More actions · Answer 9

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!