SSIS distributed transaction support in dotConnect for PostgreSQL

[Mikhail.Gvozdev]

Hi DevArt folks :),

Could you please let me know if dotConnect for PostgreSQL supports distributed transactions in SSIS that use MSDTC?

I have a test SSIS package with a sequence container and two Data Flow tasks in it. TransactionOption for the sequence container is set to Required, and TransactionOption for Data Flow tasks is set to Supported.
Both Data Flow tasks perform bulk-load of data into destination tables, the first one contains valid SQL, and the second one contains purposely invalid SQL (attempt to insert Null into the Not Null column).

When I test this scenario Using SSIS 2017 with MS SQL Server 2017 - it works fine, the whole distributed transaction is aborted, and no data make its way to the destination tables.

However, when I do the same test using SSIS 2017 with PostgreSQL 9.5 and dotConnect for PostgreSQL 7.14.1437.0, the test fails as expected, SSIS reports that it is aborting distributed transaction for the component, but when I check data in the destination - the first table gets populated.

Could you please let me know whether this is the designed behavior, or I am missing some configuration settings in the connection managers?

Thanks!

Regards,

Mikhail

[Shalex]

dotConnect for PostgreSQL doesn't support distributed transactions in SSIS.

[Mikhail.Gvozdev]

Hi Shalex, thanks for the reply and clarification, much appreciated :)!

Regards,

Mikhail