Stored procedure with result sets
I have a oledb source which uses a string variable to build up a procedure call. The stored procedure has temp tables so the call has a with result sets defined for the expected metadata. This works fine in a SSIS package, I have reverse engineered this package but cannot get BimlExpress to now emit it due to an error of:
EXECUTE statement failed because its WITH RESULT SETS clause specified 7 column(s) for result set number 1, but the statement sent 1 column(s) at run time. -1 -1
Having run a profile trace and tested the SQL captured this error is due to the stored procedure call being wrapped with SET FMTONLY ON by the BIML engine whilst doing the emit of the package.
How can I get around this within BIML?