Varchar(max) stores only about 23678 characters

1

I found online that the maximum storage size of a varchar(max) is 2^31-1 bytes (2 GB).

Why the data was truncated when it is over 23678 characters?

I query a byte array column in Postgre table in SSIS. The data is transferred and stored in SQL Server column with varchar(max). It stores only up to 23678 characters.

Any suggestions what it can be done?

Thanks.
Dany

2

A byte array in PostGres should be transferred as a varbinary(max) instead of varchar(max). An embedded 0x00 byte could be truncating the string.

Depending on which driver you're using to connect to PostGres it may also have a limit or otherwise truncate. (Not an SSIS expert).

3

Thanks robert_volk. When it was transferred as a varbinary(max), it seemed the whole length was transferred over. But when it is cast as varchar(max), the result was "’". Please see the image.

The columns store json data.

Maybe I need help on converting from varbinary(max) to varchar(max). Thanks.

varbinary
varbinary.PNG910×199 10.8 KB

4

You'd have to look at the raw bytes. Save them to a file and open in a hex editor to see how they are interpreted. Not all bytes streams can be converted to character strings.

5

You have to encode/decode the value in PostgreSQL before exporting to SSIS. There are methods available to encode the data as JSON or character data.