January 9, 2009 at 5:43 pm
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!
February 10, 2009 at 10:22 pm
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
February 19, 2009 at 12:17 pm
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
February 2, 2010 at 7:46 pm
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
Change is inevitable... Change for the better is not.
November 23, 2010 at 3:56 am
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 !!!!
June 27, 2012 at 9:38 am
EXCELLENT ARTICLE !!!!!
Saved my day.
Thank Michael Cape
http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/65112/
October 22, 2013 at 6:20 pm
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.
October 22, 2013 at 6:28 pm
http://technet.microsoft.com/en-us/library/ms188332.aspx
Rob Schripsema
Propack, Inc.
June 26, 2019 at 6:17 pm
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 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy