Postgresql malformed literal array in SSIS.

Question

Greetings. Attempting to export SQL Server data into postgresql using SSIS. I can export date or int data without issue, but attempting to export a couple fields of character data both result in this:

[ODBC Destination [2]] Error: Open Database Connectivity (ODBC) error occurred. state: '22P02'. Native Error Code: 1. ERROR: malformed array literal: "c2000";
Error while executing the query

The source is nVarchar(128), and the destination is character varying [] 128.

I set the IS Package to redirect failed rows to a text file and ever row wound up there. Here is an example of the output:

2020-12-21,c,C,,,,,,2015,,,,,8,0

Note the commas immediately after the "c" and the "C". Now matter the actual value after the first character in the source table, there is a comma in the output file.

If I change the package to not include these two character columns and only include the date and int column, they succeed. Also note this is a very simple Data Flow Task, nothing fancy at all.

Please advise, thanks!

Answer 1

Hi @chrisrdba ,

From the error message, it seems the error is related with the data type Postgresql.

Have you tried to use Data Conversion to convert DT_WSTR to DT_STR?

Not familiar with the data type of Postgresql.

You may refer arrays.html or postgresql-comparing-arrays-results-in-malformed-array-literal-errorfor more details or raise the error to the Postgresql Forum.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October