Issue With Dynamic SQL Stored Procedure And SSIS OLE DB Data Reader

We are writing SSIS packages to pull data from MSO€™s through a stored procedure and drop the output into the pipeline to store it on our local database.

Here is an example of a very simple data flow task that executes the following query in step 1

EXEC GetCustomerData @SELECT = 'OrganizationID', @FROM = 'Organization',@WHERE = 'StateOrProvinceCode = ''WA'''

This command is stored in a variable called SQLOrgCommand.
When I open the task and click on Columns I do not see any columns returned
This allows me to conclude that the metadata needed from the GetCustomerData is not understood by SSIS because the SP uses dynamic sql.


While profiling we realized that it executed the following SQL:

SET FMTONLY ON


to get the metadata. However when we ran the sp with FMTONLY ON we recieved the following errors

Msg 536, Level 16, State 5, Procedure sp_GetData, Line 100
Invalid length parameter passed to the SUBSTRING function.
Msg 536, Level 16, State 2, Procedure sp_GetData, Line 101
Invalid length parameter passed to the RIGHT function.
Msg 536, Level 16, State 5, Procedure sp_GetData, Line 116
Invalid length parameter passed to the SUBSTRING function.
Msg 536, Level 16, State 2, Procedure sp_GetData, Line 117
Invalid length parameter passed to the RIGHT function.
Msg 536, Level 16, State 5, Procedure sp_GetData, Line 187
Invalid length parameter passed to the SUBSTRING function.

The same stored procedure executed without FMTONLY OFF generates the proper output.

Is this a known bug in SQL Server? If so is there a patch that we can install.