SSIS 2005 cannot convert between unicode and non-unicode

[dolezalpaul]

Hi,

when I want to process a conversion from MS SQL Server 2005 data source to a Postgre DB I get an error: "Column "<NameOfTheColumn>" cannot convert between unicode and non-unicode string data types."
I tried to change all possible encodings (UTF8, LATIN1, WIN1250, WIN1252) in the destination Posgtre DB and the result is still that same error.
My source MS-SQL collation, that I cannot unfortunately change, is set for "SQL_Latin1_General_CP1250_CI_AS" and data type is char(50) or varchar(50).
Thanx for your response.
Pavel

[Moderator]

Hello Pavel,
the issue is reproduced and we are working on the fix...

We've noticed that wide-char fields work (nchar and nvarchar). This is only issue with char and varchar fields in MSSQL.

Thank you,
Konstantin

[aclemente]

Hi I recive this error when try to insert record from DB sql server 2008 to db Postgres (UTF8) (linked server)
with trigger on sql server.
The problem occurs only on text fields while the numeric fields are working properly (varchar nchar char)
---------- Client SQL ------------
INSERT INTO "public"."aExams"("idExam","Exam","Description","idManufacturer") VALUES (1,'100','Test',1)
---------- Error ---------
ERROR: invalid byte sequence for encoding "UTF8": 0x00
HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

any suggestions?

[Moderator]

We have fixed this bug in version 1.3 but have not ported the fix back to 1.2.8. However, if porting the fix into 1.2.8 is needed we would do it.

Could you please try 1.3.0.2096?

[rshapiro]

I am getting this same error on an INSERT from SQL Server 2005 (64-bit) using PGNP-1.3.0.2132.exe. Here is my statement that references the linked server.

insert into [openmfg-test].[openmfg-test].api.contact (first,middle,last) VALUES ('rebecca','test','shapiro')

Error message:
OLE DB provider "PGNP" for linked server "openmfg-test" returned message "ERROR: invalid byte sequence for encoding "UTF8": 0x80
HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "PGNP" for linked server "openmfg-test" could not INSERT INTO table "[openmfg-test].[openmfg-test].[api].[contact]".

The database server collation is SQL_Latin1_General_CP1_CI_AS

[Moderator]

I was able to reproduce the bug when at least one of the fields had type "text". I have fixed the bug and sent you the licensed installation module (build 2136).

Please let us know if you notice any defects or will have any questions.

Thank you!