Why in the world that I am getting these errors? SQL Server Agent job works in SQL Server 2008R2 but not in 2016

I am upgrading SSIS packages and SQL Server jobs and migrating them to SQL Server 2016. I have a data flow task that uses the below stored procedure as a source and loads it directly to the destination table. The package works perfectly as expected in visual studio 2016 but fails with the below error when run as a job in SQL Server 2016. However, In the same job, I've other job steps that include ssis packages that run perfectly fine, but those don't use dynamic sql.

Background information: Package works perfectly fine in visual studio 2008 and also as a job in SQL Server 2008R2. Package was upgraded to Visual studio 2015 and it works perfectly fine when executed in visual studio 2015, but fails when run as a job in sql server 2016.

`USE [MyDb]
    GO




/****** Object:  StoredProcedure [load].[pr_Transfer_vo_DrugExposure]    Script Date: 6/27/2017 11:29:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [load].[pr_Transfer_vo_DrugExposure] @RegistryName VARCHAR(10), @ETLLogID Int,@debug BIT = 0
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
/**************************************************************************************************
Project:        EDW2.0
JIRA:           ?
Developer:      zbachore
Date:           2016-07-14
Description:    This stored procedure just selects rows from DrugExposure table with 
                LoadStatusID = 2. The SSIS package will use the result set. The reason we use this
                here is because the result set is dynamic - by passing RegistryName as a parameter
___________________________________________________________________________________________________
Example: EXEC load.pr_Transfer_vo_DrugExposure 'ICD', 5
___________________________________________________________________________________________________
Revision History
Date            Author          Reason for Change

-------  ------- -----------------------------------------------------------------------
***************************************************************************************************/
--Variables:
DECLARE @ErrorMessage VARCHAR(2000) = ' ',
        @DrugExposure VARCHAR(100) = @RegistryName + '_vo.DrugExposure',
        @SelectSQL VARCHAR(max)

BEGIN TRY

IF 1 = 0  -- this is done only to make SSIS recognize the columns
SELECT [DrugExposureKey]
      ,[VisitOccurrenceKey]
      ,[ClientPatientID]
      ,[ClientPatientKey]
      ,[SubmissionKey]
      ,[RegistryElementID]
      ,[DrugConceptID]
      ,[DrugAdminConceptID]
      ,[EffectiveDrugDoseConceptID]
      ,[RouteConceptID]
      ,[Dosage]
      ,[DoseUnitConceptID]
      ,[NullFlavorConceptId]
      ,[Quantity]
      ,[ETLLogIDInsert]
  FROM [icd_vo].[DrugExposure]



ELSE
BEGIN

SELECT @SelectSQL = '
SELECT [DrugExposureKey]
      ,[VisitOccurrenceKey]
      ,[ClientPatientID]
      ,[ClientPatientKey]
      ,[SubmissionKey]
      ,[RegistryElementID]
      ,[DrugConceptID]
      ,[DrugAdminConceptID]
      ,[EffectiveDrugDoseConceptID]
      ,[RouteConceptID]
      ,[Dosage]
      ,[DoseUnitConceptID]
      ,[NullFlavorConceptId]
      ,[Quantity]
       ,ETLLogIDInsert = ' + CAST(@ETLLogID AS VARCHAR(10)) + '
  FROM ' + @DrugExposure + ' de
  INNER JOIN etl.ETLSubmissionQueue sq ON de.SubmissionID = sq.SubmissionID
  WHERE sq.LoadStatusID = 2'

   IF @debug = 1
  PRINT @SelectSQL
  ELSE
  EXEC(@SelectSQL) 
  --PRINT @SelectSQL --for debugging only

  END
    END TRY
    BEGIN CATCH
        set @ErrorMessage =  'An error occurred in stored procedure load.pr_Transfer_vo_DrugExposure ' + ERROR_MESSAGE()
        RAISERROR (@ErrorMessage,
               16,
               1 
               );
    END CATCH



SET NOCOUNT OFF;
END

The following is the error I am getting when executing the job:

Executed as user: MyDomain\myUser. Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 8:34:31 AM Error: 2017-06-27 08:34:32.34 Code: 0xC0202009 Source: Transfer Drug Exposure vo_DrugExposure Source [55] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'EXEC(@SelectSQL)' in procedure 'pr_Transfer_vo_DrugExposure' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.". End Error Error: 2017-06-27 08:34:32.34 Code: 0xC020204A Source: Transfer Drug Exposure vo_DrugExposure Source [55] Description: Unable to retrieve column information from the data source. Make sure your target table in the database is available. End Error Error: 2017-06-27 08:34:32.34 Code: 0xC004706B Source: Transfer Drug Exposure SSIS.Pipeline Description: "vo_DrugExposure Source" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2017-06-27 08:34:32.34 Code: 0xC004700C Source: Transfer Drug Exposure SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2017-06-27 08:34:32.34 Code: 0xC0024107 Source: Transfer Drug Exposure Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:34:31 AM Finished: 8:34:32 AM Elapsed: 0.5 seconds. The package execution failed. The step failed.

I appreciate any help. Please let me know if you have any questions or need clarification.

Thank you very much! Zed

I have the same problem upgrading from SSIS 2008 to SSIS 2014. Works fine in Data Tools but fails in production. The solution is explicitly stated in the error message :)