Today I encountered an error when using executing a stored procedure as SQL Command in an OLEDB source. The error referred to the stored procedure using a temporary table. Apparently, the OLEDB source doesn’t play nice when stored procedures use temporary tables, however the OLEDB source does work when stored procedures contain table variables and CTE’s. A quick work around that doesn’t involve rewriting your stored procedure is to define the metadata in the execution command.
EXEC usp_YourProcedure WITH RESULT SETS ((MaterialNK nvarchar(50) NOT NULL, MaterialDescription nvarchar(200) NOT NULL , MaterialType nvarchar(50) NOT NULL, StartDate datetime NOT NULL, EndDate datetime NULL))
Note that when defining your columns that they must be in the same ordinal position as defined in the stored procedure. Changing the ordinal position in your metadata definition will NOT change the ordinal position, but rather it will simply change the column names returned by your query.