SQLTeam.com | Weblogs | Forums

SSIS and PostgreSQL data

SSIS and Import/Export
#1

I am trying to import a table from PostgreSQL to SQL Server. One of the fields in the Postgre table is "bytea" data type. When I open the table in dBeaver, that column contains json data.

I created an ODBC system user that connects to PostgreSQL for SSIS. When I viewed the table data using SSIS ODBC Source, it shows encrypted data for that field. After the data was imported to SQL Server table varbinary(max) column, it contains the encrypted data. I cast the data to varchar(max) "cast([bytes] as varchar(max))". It returns "’", no json data.

My question is
(1) Why SSIS ODBC Source shows encrypted data instead of the original json data?
(2) What has to be done in order to bring in the original json data?

Thanks.
Dany

#2

What if you tried varbinary data type bytea os binary data in PG

#3

Thanks. I cannot modify Postgre tables.

#4

I am talking about your ssis on the SQL side

#5

It is varbinary in sql. The issue is the data shows encrypted in SSIS ODBC Source before it is imported to SQL Server.

#6

:sleeping: sorry totally missed that.

#7
  1. The data is encoded in PostgreSQL - and SSIS is showing the data in its actual format. DBeaver understands this encoding and is decoding the values for you.
  2. You need to decode the value in PostgreSQL on output to SSIS so the JSON data is passed as text.

I am not familiar enough with PostgreSQL - nor with JSON - but it seems to me they have functionality available that will convert the bytea data to JSON which can then be passed directly to SSIS.

#8

We figured it out.
select encode(Col1,'escape')

but encode truncates long bytea. :frowning: