Here’s another annoying thing that might happen when a stored procedure is too ‘complex’ for SSIS:
"The metadata could not be determined because every code path results in an error..."
"Unable to retrieve column information from the data source. Make sure your target table in the database is available"
Microsoft SQL Server 2012 extends the EXECUTE statement to introduce WITH RESULT SETS option which can be used to change the Column Name and Data Types of the result set returned by the execution of stored procedure.
Example Using WITH RESULT SETS Feature of SQL Server 2012
Example - Using WITH RESULT SETS Feature of SQL Server 2012
WITH RESULT SETS
[Employe Name] NVARCHAR(100),
[Employee City] NVARCHAR(20),
[Employee Postal Code] NVARCHAR(30)
The WITH RESULT SET Feature of SQL Server 2012 is a great enhancement to the EXECUTE Statement. This feature will be widely used by Business Intelligence Developers to execute a stored procedure with in an SQL Server Integration Services (SSIS) Package to return the result set with required Columns and modified data types.