how to solve the execution of my ssis package in debug mode which sometimes works and sometimes fails?

Question

Hi team,

I created an ssis package with data sources (oracle 64 bits) and destination (sql server 64 bits) ok. in fact through an sql query I want to export data from an oracle table to an sql server table.

at this level everything is fine.

Using visual studio vs2022 64bits 17.6.4 in debug mode:

• sometimes it works (i can see the data in sql server table.)
• and sometimes running a second time the ssis package fails after deleting all data in sql server table (I do it to check if everything is working fine):

SSIS package "C:\REs\package.dtsx" starting. Information: 0x4004300A at Tâche de flux de données 2, SSIS.Pipeline: Validation phase is beginning. Information: 0x4004300A at Tâche de flux de données 2, SSIS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Tâche de flux de données 2, SSIS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Tâche de flux de données 2, SSIS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Tâche de flux de données 2, SSIS.Pipeline: Execute phase is beginning. SSIS package "C:\REs\package.dtsx" finished: Canceled.

I don't understand this behavior. I don't understand why sometimes the execution works and sometimes not.

Subsequently, I made a faultless deployment of the ssis package to sql server for scheduled automatic execution. But when execution is started, I receive the following message:

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 15.0.2000.5 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved. Started: 16:56:00 Package execution on IS Server failed. Execution ID: 20, Execution Status:6. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report Started: 16:56:00 Finished: 16:56:05 Elapsed: 4,734 seconds. The package execution failed. The step failed.

In the Integration Services Catalog reports, I don't see any alarming error messages apart from unexpected termination.

what to do?

Thank you for your support

Answer 1

Hi @user,

It's hard to debug this only with the information provided, but in general please make sure that the account used to run the agent job has full access to your oracle source and sql server.

Please also make sure that you have set to run package in 64bit.

In addition, what's your version of SQL Server?

To execute SSIS packages targeting SQL Server 2019 and above, you do not need to install an Oracle client to use the Microsoft Connector for Oracle. To execute SSIS packages targeting SQL Server 2017 and below, in addition to Microsoft Connector for Oracle, you will need to install an Oracle client and Microsoft Connector for Oracle by Attunity.

Check Microsoft Connector for Oracle

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

thank you all for your support.

I was able to solve all my problems.

here are the actions performed:

• updated tools used: sql server 2019 CU23, Microsoft Visual Studio Community 2022 Version 17.8.1, SQL Server Data Tools 17.8.119.0, SQL Server Integration Services 16.0.5131.0, windows server 2019 64 bits version 1809
• installation of microsoft connector for oracle (https://www.microsoft.com/en-us/download/details.aspx?id=104113)
• sql server configuration manager > sql server services > sql server and sql server agent connect via local administrator
• creation of the ssis package: source used (MS oracle source) and destination used (Destination for sql server)
• properties of my ssis package: creatorname (local administrator), potectionlevel (userkey), targetserverversion (sql server 2022 but if i change to sql server 2019 then errors), run64bitruntime (true)

so the execution of my job is happening normally.

however I would like to know why my package does not execute normally when I change the targetserverversion property to sql server 2019 yet I use sql server 2019?

for the rest everything works fine when I switch the targetserverversion property to sql server 2022; my ssis package and my job runs normally

thank you all for your support.