Using a Stored Procedure as a Data Source in SSIS or SSRS

We often like to use stored procedures to return our result sets because we can easily aggregate data together and perform multiple steps in an organized way.  However, SSIS and SSRS both see result sets a little differently than you might think.  Many stored procedures have several SELECT statements to gather data together.  The first few SELECT statements are often to gather parameters or collect data into table variable or temp tables.  SSIS and SSRS both define the meta-data for the result set of the stored procedure as the column names and data types of the 1st SELECT statement in the stored procedure, regardless of whether it is executed or not.  Therefore, a trick to make sure the stored procedure will work for SSIS and SSRS is to include a conditional block at the beginning of the stored procedure that defines the result set column names and data types.  This might look something like this:

IF 1=0 BEGIN -- notice this will NEVER be executed, but it does define the first SELECT statement....

   SELECT

CAST(NULL AS int) as colID,

CAST(NULL as VARCHAR(20)) AS someCode,

CAST(NULL AS DECIMAL(8,2)) AS someDecimal

END