SQL 2000 DTS "Middle of String" transform issue with Unicode data?
-
I have a DTS load (SQL Server 2000) that has started exhibiting a problem when loading data from pipe-delimited flat files (Unicode UCS-2) to a staging table on SQL Server. The problem only seems to pop up when I am loading data from a single-character field in the source file to a single character (nchar(1)) column in the target table.
The source fields are populated with "Y" or "N". The corresponding targets are ending up with what visually appears to be null values, but checking for nulls in these results does not return the expected rows. Nor do they appear to contain empty strings.
The Middle of String transform is being used to push the data from source to target, starting at position 1, limited to 1 character, trimming any leading or trailing white space. The source value in the field contains |Y| or |N|. If I use Middle of String, the result in the target is something that is not a viewable character, (maybe the first of the two bytes in a single character unicode value?). If I switch from Middle of String to Copy Column, the source value gets in properly. But I can't use that option due to data quality issues from the various sources that produce the source files for this load. I need to be able to use Middle of String.
We have an analogous ANSI version of the load process, where everything is the same other than the encoding of the source file (ANSI versus UNICODE). That load works fine. Also, within the Unicode version of the load, only the fields/columns that are 1 character wide seem to be exhibiting this issue. Other fields that are wider seem to load just fine, even if they contain unicode data, using Middle of String.
I tried changing the transform's "Limit number of characters" property to 2, but it didn't make any difference. Only thing that seems to work is Copy Column, and again that is not a viable solution.
Has anyone else come across this type of issue? Anyone find a resolution?
Thanks
-
Hey Frank,
Good description of your problem - so good that I even managed to recreate the problem!! A little experimentation got me to this conclusion:
Open your Middle of String transformation for your Y/N characters and change the "Start Position (1 based)" option to 0 (zero) and don't check the "Limit Number of Characters to" option in the Substring Options.
This should solve it - let me know how it goes.
-
Hi Adrian
Thanks for the suggestion! Seems to work like a charm. I found that it worked fine just by changing Start Position (1 Based) to zero. Left the "Limit number of characters to" at 1.
Of course, this makes little sense on several levels...("Start Position (1 Based)" needs to be zero??
), and we have other fields that are wider than one character that use the same transform logic (other than the Limit number of characters to value), and it's not like those lop off the first character in the result. That's M$ for you. In any case, thanks for the assist."Frank" (aka Joe)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply