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 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply