I have a column in Postgres that is declared as a JSONB datatype and I am attempting to move data from one PostgreSQL server to another using SSIS (cannot clone DB in current environment). The data is stored in UNICODE. I am using the
Intellisoft OLE DB Provider for PostreSQL OLE DB Provider - it can be found here: https://www.pgoledb.com/index.php/purchase. I have a trial version of the Desktop Edition.
What is the correct datatype to use in SSIS when loading a JSONB datatype into a PostgreSQL server? Am I using the correct OLE DB Provider?
I can get the JSON data into the data flow and cast the data as either DT_WSTR (Length 4000) or DT_NTEXT without any problems. But, when I try to load it to the destination PostgreSQL server, I get the following error on the destination component.
[OLE DB Destination ] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Intellisoft OLE DB Provider for PostgreSQL" Hresult: 0x80004005 Description: "ERROR: column "ColumnName" is of type jsonb but expression is of type character varying LINE 1: ...$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30)
I tried performing a data conversion on the both the DT_NTEXT and DT_WSTR to DT_BYTES.
For the DT_NTEXT, that does nothing because when I open the data conversion component back up again, it automatically changes the conversion back to DT_NTEXT.
For the DT_WSTR, I set the DT_BYTES length to 4000, but that results in a truncation error on the conversion component (see error message below).
[Data Conversion ] Error: Data conversion failed while converting column "ColumnName" (143) to column "Copy of ColumnName" (6). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
I tried wiring a Source and destination component where the source is set to Table View, meaning there was no SQL command. On the source's Advanced Properties editor, the JSON column is being outputted as DT_NTEXT.
Looks like the OLE DB driver was the problem. A patch released today adds support for the JSONB data type. Upgrade to version 184.108.40.20690.
User contributions licensed under CC BY-SA 3.0