Character encoding issues with text fields

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

Character encoding issues with text fields

Postby NickDech » Mon Mar 04, 2013 9:40 pm

I'm having a hard time inserting text fields using the driver with SSIS. If I insert rows across the linked server, they insert fine and I have no encoding issues, however when I use SSIS, my text fields aren't being stored properly whenever the source data type is DT_NTEXT. ( If I use DT_WSTR my data gets truncated at 255 characters but the characters are stored correctly).

Text that is supposed to this:
"†As rated by engine manufacturers.
*#07124043 A $109.99 value. Offer valid thru 3/17/12.
††Plus any attachment purchased with tractor will be delivered and set up for free
If you find a tractor, mower or line trimmer at your local Lowe’s or The Home Depot store with a lower price (at least $1.00 lower) and the same features as the comparable Craftsman model (as determined by the Sears Yard Guru tool available at www.searsyardguru.com), then Sears will match the price and give you a gift card up to $100 for use on a future purchase. ($50 for eligible items $1,299.99 and below, $100 for eligible items $1,300.00 and above). Customer must purchase the qualifying Craftsman model mower, or tractor to be eligible. See store or sears.com for full details."

Appears in Postgres as this ( shortened for brevity ):
"<\\000p\\000l\\000u\\000s\\000>\\000A\\000s\\000 \\000r\\000a\\000t\\000e\\000d\\000 \\000b\\000y\\000 \\000e\\000n\\000g\\000i\\000n\\000e\\000 \\000m\\000a\\000n\\000u\\000f\\000a\\000c\\000t\\000u\\000r\\000e\\000r\\000s\\000.\\000\\134\\000\\134\\000r\\000\\134\\000\\134\\000n\\000*\\000#\\0000\\0007\\0001\\0002\\0004\\0000\\0004\\0003\\000 \\000A\\000 \\000$\\0001\\0000\\0009\\000.\\0009\\0009\\000 \\000v\\000a\\000l\\000u\\000e\\000.\\000 \\000O\\000f\\000f\\000e\\000r\\000 \\000v\\000a\\000l\\000i\\000d\\000 \\000t\\000h\\000r\\000u\\000 \\0003\\000/\\0001\\0007\\000/\\0001\\0002\\000.\\000\\134\\000\\134\\000r\\000\\134\\000\\134\\000n\\000<\\000p\\000l\\000u\\000s\\000>\\000<\\000p\\000l\\000u\\000s\\000>\\000P\\000l\\000u\\000s\\000 \\000a\\000n\\000y\\000 \\000a\\000t\\000t\\000a\\000c\\000h\\000m\\000e\\000n\\000t\\000"


Anyone have any ideas?
NickDech
 
Posts: 6
Joined: Thu Feb 28, 2013 9:32 pm

Re: Character encoding issues with text fields

Postby Moderator » Mon Mar 04, 2013 10:04 pm

I will try to reproduce it. Could you send any details on table(s) DDL, test package, etc. to Support? It will reduce amount of time for us to reproduce and fix the bug. Thank you!
User avatar
Moderator
Site Admin
 
Posts: 302
Joined: Wed Oct 29, 2008 11:27 pm

Re: Character encoding issues with text fields

Postby NickDech » Tue Mar 19, 2013 11:52 am

An update to this;

I was unable to get around this encoding issue for any varchar field above 4000 characters. Once my destination data type became larger than nvarchar(4000) ( a text blob ), the encoding issues would come up.
NickDech
 
Posts: 6
Joined: Thu Feb 28, 2013 9:32 pm

Re: Character encoding issues with text fields

Postby Moderator » Mon Apr 15, 2013 2:14 am

We added support for COPY WITH BINARY, and reworked the insertion related code. It affected FastLoad and BULK_METHOD=COPY.

When a Greenplum database is detected, the provider falls back to COPY WITH CSV mode.

Please use build 3142 or later.
User avatar
Moderator
Site Admin
 
Posts: 302
Joined: Wed Oct 29, 2008 11:27 pm


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 2 guests

cron