DT_Str and trailing whitespace?

I have a OLE DB Source going to a flat file destination. My source is a sql variable with "select * from tablename" which have varchar datatypes. Yet I'm getting trailing spaces at the end of some of my columns (for instance, my address column).

I've checked the data by doing a "select Max(Len(address)) from tablename" and the max is only like 34 chars, yet each of them have 100 chars total.

Taking a look at my flat file connection, the outputColumnWidth is 100, and datatype is string [DT_STR]. Am I crazy? What's the problem here? Is the DT_STR datatype the equivalent of char, and not varchar?

Any help, of course, will be appreciated.
LEN does not count trailing spaces.

Try this: select max(len(replace(address,' ','*')))
|||What about using a derived column in the dataflow using ltrim/rtrim/trim function?|||Are you using a fixed length flat file format, or a delimited format?|||Yeah, are you doing a fixed width format on your file? Please share the format of your destination flat file. Fixed width will be 100, in this case. No way around that.|||Thanks for the responses...

The flat file destination is delimited, not fixed width.

As I checked the select max(len(replace(),' ','*')), I see that the data is bad... thanks so much for the help!!! Think that should fix it.

|||I'm curious to know what's bad about the data. Varchars don't store trailing spaces so I'm curious to just know the end result.|||Basically I have about 4 SSIS Packages taking data through a long set of processes. In one of the initial processes, I found that the address field was being stored as a char(500). Later on in the process, it's run through a data conversion that put it as a varchar(100), which is where it remained through the rest of the packages. But at this point, I guess, the trailing spaces were already in there, and remained in there.

I had thought the data was fine this whole time, mainly because when I used the TSQL len() function, the data seemed fine. Same with in Management studio, I'd double-click the separators between column headers to let it auto-resize the column to fit the largest address, without spaces at the end.

Glad I found it... thanks for all the help. =)

DT_NTEXT pass through columns in fuzzy lookup transformation

The documentation on the fuzzy lookup transform mentions that only columns of type DT_WSTR and DT_STR can be used in fuzzy matching. I interpreted this as meaning that you could not create a mapping between an input column of type DT_NTEXT and a column from the reference table. I assumed that you could still have a DT_NTEXT column as part of the input and mark this as a pass through column so that it's value could be inserted in the destination, together with the result of the lookup operation. Apparently this is not the case. Validation fails with the following message: 'The data type of column 'fieldname' is not supported.' First, I'd like to confirm that this is really the case and that I have not misinterpreted this limitation.

Finally, given the following situation

- A data source with input columns

Field_A DT_STR
Field_B DT_NTEXT

- A fuzzy lookup is used to match Field_A to a row in the reference table and obtain Field_C.

- Finally, Field_B and Field_C must be inserted into the destination.

Can anyone suggest how this could be achieved?

Fernando Tubio

One possible workaround is using a multicast transform to route the input columns around the lookup transform. A merge join transform can then be used to join the outputs from the multicast and the fuzzy lookup to include the DT_NTEXT field back into the data flow.

I've tried this solution and it works but I wonder if it is really necessary to resort to all these contortions.

|||

It looks like your workaround is the best approach. The Fuzzy lookup does not support DT_NTEXT, DT_TEXT, or DT_IMAGE columns as copy columns OR pass-through columns. I am not sure why that is, but I will try to find out.

Mark

|||

I guess one of the reasons was performance, and that these columns require special handling. You might want to put in a request for this feature for a future release.

Thanks
Mark

|||

Thank you Mark.

Considering my limited knowledge about the inner workings of the data flow pipeline I am very likely wrong, but I would have guessed that a pass-through operation merely involved copying some pointers around. In any case, the package creator can control which columns to pass-through and if he is concerned with performance, then he is in a better position to decide whether to include these columns in the output. So I guess it would be nice to have this choice in a future release.

Fernando Tubio