Bulk Insert on Postgres table with auto-increment UUID field fails

Discussion of issues, suggestions and bugs of Devart SSIS Data Flow Components, our product line for building SSIS-based ETL solutions, performing data access to popular cloud applications and databases.
Post Reply
vmccormick
Posts: 3
Joined: Wed 19 Dec 2018 19:01

Bulk Insert on Postgres table with auto-increment UUID field fails

Post by vmccormick » Wed 09 Jan 2019 15:55

We have a table in Postgres that uses an auto-increment on a UUID field. We tried to bulk insert into that table and exclude the field with the auto-increment and the bulk insert fails. What is the best way to handle this?

vmccormick
Posts: 3
Joined: Wed 19 Dec 2018 19:01

Re: Bulk Insert on Postgres table with auto-increment UUID field fails

Post by vmccormick » Thu 10 Jan 2019 15:55

After some testing we were able to complete a bulkinsert into a test table with the same schema and column types. We think maybe the package has a schema issue. We are revisiting the original SSIS package. Thanks.

Pinturiccio
Devart Team
Posts: 2357
Joined: Wed 02 Nov 2011 09:44

Re: Bulk Insert on Postgres table with auto-increment UUID field fails

Post by Pinturiccio » Fri 11 Jan 2019 14:59

Can you confirm that the issue is solved?

vmccormick
Posts: 3
Joined: Wed 19 Dec 2018 19:01

Re: Bulk Insert on Postgres table with auto-increment UUID field fails

Post by vmccormick » Fri 11 Jan 2019 15:09

Here is what the developer sent today. Can you confirm that this has to do with the destination component?

We are also testing some settings. Bulk insert uses text or binary setting, and also there is a Unicode setting that may effect the results. We have that set to false when I know we are using Unicode in the Postgres database.

So I just found out what the issue is with BulkInsert mode…it looks like when the Postgres data type is SMALLINT, the bulk insert fails because it’s trying to insert a NULL to the id column.

If I change the data type to INTEGER, the bulk insert works. I just tested this on the table changing the id column back and forth from SMALLINT to INT.

Looks like it’s a bug with their destination component.

Pinturiccio
Devart Team
Posts: 2357
Joined: Wed 02 Nov 2011 09:44

Re: Bulk Insert on Postgres table with auto-increment UUID field fails

Post by Pinturiccio » Wed 16 Jan 2019 15:10

We have reproduced the issue with a smallserial primary key. We will investigate it and post here about the results as soon as possible.

Post Reply