Varchar(number) causes failure in SSIS

Question

I'm developing a pretty straightforward SSIS package: run a stored procedure to export data from a variable table directly into a flat file destination. Originally, each column in the variable table was defined as varchar(max), worked fine so far. For improve the performance, I decided to apply constraint to the variable table, which is not compatible with varchar(max) and I had to assign a certain number to it, e.g. varchar(10). After this change, this SSIS suffered from a series of error:

Error: 0xC0202009 at ESP AL Extract, OLE DB Source [835]: An OLE DB error has
occurred. Error code: 0x80040E21.
An OLE DB record is available.  Source: "Microsoft SQL Native Client" 
Hresult: 0x80040E21  Description: "Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC0208265 at ESP AL Extract, OLE DB Source [835]: Failed to retrieve
long data for column "SR_RequestID".
Error: 0xC020901C at ESP AL Extract, OLE DB Source [835]: There was an error 
with output "OLE DB Source Output" (846) on component "OLE DB Source" (835).
The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at ESP AL Extract, OLE DB Source [835]: The "output
"OLE DB Source Output" (846)" failed because error code 0xC0209071 occurred,
and the error row disposition on "component "OLE DB Source" (835)" specifies 
failure on error. An error occurred on the specified object of the 
specified component.

The flat file columns data type is string[DT_STR]. Any thought is appreciated.

Answer 1

Try this :-

Right Click OLEDB Source and select Show Advanced Editor.In the Input Output Properties expand the Oledb Source output and Output Columns.Select the column and change the data type to String[DT_STR] with the length of 10.

Reason you are getting an error is because when you first created your stored procedure with the data type varchar(max) ,SSIS converted the column to DT_TEXT.So once the column data type was changed to varchar(10) ,SSIS failed to convert varchar to text stream .Hence failed .