Nested Transaction Issue

Hi,

I am developing a SQL 2008 R2 SSIS package to transfer data from SQL Server 2008 R2 to Redshift within a transaction.

I am using the latest PGNP driver (v1.4.0.3392) and connecting to Redshift. The connection has the RetainSameConnection set to True.

[attachment=0:16v67gov]<!-- ia0 -->SSIS Control Flow.jpg<!-- ia0 -->[/attachment:16v67gov]

When running the package I am encountering the following issue:

[i:16v67gov]

---

Client SQL 2015/11/20 10:54:18.289 4CF57A66

---

ROLLBACK TO SAVEPOINT PGNP_NESTED_TRANS

---

Error

---

Abort failed: conn=0000000008136B90 ERR=7 'ERROR: SQL command &quot;ROLLBACK TO SAVEPOINT PGNP_NESTED_TRANS&quot; not supported.
'[/i:16v67gov]

Additional error message added for information:
[i:16v67gov]ERROR: opening multiple cursors from within the same client connection is not allowed.[/i:16v67gov]

Any help you can give is appreciated.

This looks like a defect. We are currently working on a fix. I will update this thread about the fix availability soon. Thank you!

Also, regarding &quot;ERROR: opening multiple cursors from within the same client connection is not allowed&quot;, two workarounds could help:
1. Upgrade Redshift cluster to higher tear that allows multiple cursors. At least this was possible in the past, I did not check recently.
2. Disable cursor in the OLEDB provider by adding &quot;CURSOR=OFF;&quot; to the Extended Properties in the connection string.

Could you try the above workarounds?

Hi,

I will try the suggestions and let you know the results.

regards,
Peter