happycat59 (3/13/2013)
Coincidentally, I had a similar problem just this morning with a sproc that calls msdb.dbo.sp_help_jobHave a look at http://connect.microsoft.com/SQLServer/feedback/details/737341/sql-server-2012-openrowset-on-msdb-dbo-sp-help-job-throws-error
In particular, the syntax used in the first workaround. The part of the query of interest to you, I think, is the syntax of the WITH RESULTS SETS where the definition of the columns that are returned by the sproc are explicitly listed.
Hope this help you
Yep - WITH RESULT SETS is the way to fix this. It's not really a workaround, in my opinion, rather an improvement to the way in which stored procs and SSIS interact which means that SSIS does not have to go through the proc column sniffing.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.