Unexpected Termination SQL Server 2019 SSIS

Question

We have SSIS package that fails with Unexpected Termination but data is moved from source to target sometimes. To narrow down the issue I created simple package using visual studio 2019 with SSISDT extension and deployed it to SQL Server 2019. Package is configured with target version 2019. Package has one dataflow with source and destination moving data from SQL Server table to Oracle DB. Package execute and I see data moved to target but it fails with unexpected termination error . There are times when it runs and terminate without moving data as well. Failure seems to occur when only dealing with Oracle DB however I I have made sure 64bit oracle client is installed as our SQL Server is 64bit. Does any one have idea why it would terminate like this .

Things I have tried:

1. Grant Sys Admin access to running account.
2. We installed SQL Server 2016 on the same server and deployed the same package to SQL Server 2016 instance and it works fine no error. Issue is with SQL Server 2019 only.
3. I have tried recreating catalog as well but it has not work.
4. I have run the package as 32bit with 32bit DNS configured to Oracle db and it does work but this need to work on 64bit.

Answer 1

Hi @Divyesh Patel ,

Failure seems to occur when only dealing with Oracle DB

As you said that you are using SQL Sever 2019, could you please try to use Microsoft Connector for Oracle?

Microsoft Connector for Oracle enables the ability to export data from and load data into Oracle data source in an SSIS package.

Details you may refer: oracle-connector

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.