SSIS preserve trailing spaces in csv destination

Question

I have a problem in SSIS, with a simple data flow task between a sql SELECT and a CSV destination. I don't retrieve trailing spaces in string columns of the destination. It seems SSIS delete them.

Is there a way to preserve trailing spaces in columns of a flat file destination? Example :
data flow task :
input : sql select, data is char(8) so with spaces at right, eg "9L300 "
output : flat file, csv format, spaces at right are automatically trimed, eg "9L300"

I need to preserve trailing spaces in order to not have regression with the current export in MS DTS.

I tried this in my SQL select but no effect in the csv destination file

rtrim(MYDATA) + space(8-len(rtrim(MYDATA))) as MYDATA

Answer 1

Just check whether values are getting manipulated by OLE DB source or while along the way to flat file destination or at flat file destination manager. So observe(enable data viewer) values by using Derived Column/Data Conversion/Script Component in-between source and destination.

Give it a try by setting value of ANSI_PADDING over the database you are using for OLE DB source.

SET ANSI_PADDING