Generating a mass change to stored procedures

If we want to automatically generate the sample SSIS package for the WideWorldImporters solution, as we intended to do in the beginning, we need to work on some prerequisites. These prerequisites are so interesting and challenging that it is best to dedicate a separate recipe to them. The SSIS package from the sample WideWorldImporters solution uses Get stored procedures in the Data Flow Task OLE DB Source to retrieve data from the source. In the original package, the EXECUTE statement calling each Get stored procedure uses the RESULT SET clause to specify data types and column names for the return result set. This is necessary for SSIS to work correctly because all Get stored procedures use temp tables and since temp tables are resolved at runtime, SSIS is unable to retrieve metadata for every output column of the result dataset.

The use of temp tables is a challenge for Biml as well, and in addition to the RESULT SET clause, we need...