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.

Can you post an example of the stored proc you're using? I created a dynamic sql sproc and it seems to work just fine.