0

I created an SSIS package with Oracle data source and SQL Server destination 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.

when I run the SSIS package (to test) in debug mode using visual studio vs2022 64bits 17.6.4:

  • 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); it seems to run infinitely, this is the log:

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.

here the image: https://ibb.co/f1crvpQ

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

Subsequently, I made a deployment of the SSIS package to SQL server for scheduled automatic execution. But when execution is started, I received 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

My environment:

  • Windows Server 2019 version 1809
  • SQL Server 2019 64 bits
  • vs2022 community 64 bits 17.6.4
Improve this question
4
  • What is the output of select @@ version? There is a known issue with older releases of SQL Server 2019.
    – Greg Askew
    Nov 23 at 17:11
  • thank sir. this is the output of select @@version: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64>(Build 17763: ) (Hypervisor)
    – user11665472
    Nov 24 at 12:22
  • That is the RTM release of SQL Server 2019, from November 2019. You're four years behind on updates. At a minimum, you need to install cumulative update 23. learn.microsoft.com/en-us/troubleshoot/sql/releases/…
    – Greg Askew
    Nov 24 at 12:41
  • learn.microsoft.com/en-us/troubleshoot/sql/releases/…
    – Greg Askew
    Nov 24 at 12:42

2 Answers 2

Reset to default
0

Are you using the old Attunity Oracle Connector? That connector was deprecated in SQL Server 2019 and replaced by the Microsoft Connector for Oracle. You can install the new connector from here. You may have a different experience with the new connector.

Improve this answer
13
  • thank you for your support sir
    – user11665472
    Nov 22 at 10:35
  • -to be able to connect to an Oracle ODBC data source, I instead installed an Oracle version 21 client; I don't know if this has anything to do with re you using the old Attunity Oracle Connector? -using vs2022 64bits version 17.6.4, I created an ssis package which is supposed to import data from the oracle source (using a sql command as access mode) to the destination sql server 2019 64 bits - as I said, running in debug mode using vs2022 sometimes works and very often does not work; at this level I don't understand this behavior
    – user11665472
    Nov 22 at 10:36
  • - now by deploying the ssis package in sql server 2019 64 bits for automatic execution scheduling, here are the actions performed: creation of the new job, I gave the job a name, as owner I have my administrator account, I created a single step that uses the ssis package but I receive the following error after execution: SQL JOb Failing with The step did not generate any output. The return value was unknown. The process exit code was -1073741819
    – user11665472
    Nov 22 at 10:37
  • -I would like to say that in sql server configuration manager, for the sql agent and sql server services I used the same administrator account In view of all this, is there an incompatibility problem? as you suggested, should we absolutely use microsot connecotor instead? here is my environment: -windows server 2019 64 bit version 1809 -sql server 2019 64 bit -vs2022 community 64-bit 17.6.4 I installed an Oracle client to be able to connect to the Oracle database my ssis package uses run64bitruntime mode
    – user11665472
    Nov 22 at 10:39
  • I receive the following error after execution of my ssis package: SQL JOb Failing with The step did not generate any output. The return value was unknown. The process exit code was -1073741819
    – user11665472
    Nov 22 at 10:40
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .