SSIS Package execution failed with errors

Question

Hi,

After performing a SQL/SSIS/Oracle upgrade as per the table below, we are facing SSIS package execution issues.

Here is the upgrade path:

SQL Server 2012 to SQL Server 2019 (15.0.4153.1)

Visual Studio 2010(SSDT) to Visual Studio 2019(SSDT)

Oracle 11g to Oracle 19c

Oracle Client Version 11 to Version 19c

Attunity 2.0 to MS Oracle Connector Version 15

Window server 2012 to Windows server 2019

Packages are deployed in file system mode and are executed using SQL server agent jobs.

Below is the error message:

"SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Get DimEntity Records returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

""SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005."

Message

Executed as user: DOMAIN\UserName. ...15.0.4153.1 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved. Started: Time AM Error: Date Time Code: 0xC001000E Source: SSIS-Package-Name Description: The connection "{5028C9BE-EB4A-486F-89C9-578E0F9B6BC6}" is not found. This error is thrown by Connections collection when the specific connection element is not found. End Error Error: Date Time Code: 0xC001F208 Source: SSIS-Package-Name Description: Object name "C:\Folder\folder\packagename.dtsConfig" is not valid. Name must contain only alphanumeric characters or underscores "". End Error Error: Date Time Code: 0xC001F208 Source: SSIS-Package-Name Description: Object name "C:\SSIS\folder\packagename.dtsConfig" is not valid. Name must contain only alphanumeric characters or underscores "". End Error Error: 2021-09-22 01:57:15.06 Code: 0xC0202009 Source: SSIS-Package-Name Connection manager “ConnectionManagerName” Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01005: null password given; logon denied". End Error Error: Date Time Code: 0xC00291EC Source: Get MaxErrLogId Execute SQL Task Description: Failed to acquire connection “ConnectionManagerName”. Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: Date Time Code: 0xC0202009 Source: CDFT - Cache Entity Dimension Data Get DimEntity Records [2] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. End Error Error: Date Time Code: 0xC0202009 Source: SSIS-Package-Name Connection manager “ConnectionManagerName” Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01005: null password given; logon denied". End Error Error: Date Time Code: 0xC00291EC Source: Get MaxErrLogId Execute SQL Task Description: Failed to acquire connection “ConnectionManagerName”. Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: Date Time Code: 0xC0047038 Source: CDFT - Cache Entity Dimension Data SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Get DimEntity Records returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. End Error Error: Date Time Code: 0xC0202009 Source: SSIS-Package-Name Connection manager “ConnectionManagerName” Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01005: null password given; logon denied". End Error Error: Date Time Code: 0xC00291EC Source: Get MaxErrLogId Execute SQL Task Description: Failed to acquire connection “ConnectionManagerName”. Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: Date Time Code: 0xC0014020 Source: DFT - Load Double metaphone Population Load Data to Comm_Name_Search [84] Description: SQLSTATE: 08S01, Message: [Microsoft][ODBC Oracle Wire Protocol driver]Unexpected Network Error. Err... The package execution fa... The step failed.

Please advise what could be wrong here.

Thanks

Answer 1

Hi @RNA-5508 ,

SSIS 2019 doesn't need Attunity driver for Oracle anymore.

SSIS 2019 comes with Microsoft Connector for Oracle V1.0
It is very fast (using the best 3rd party ODBC driver behind the scenes) and it has a fantastic bonus - no need to install Oracle client.

Precondition: your SSIS run-time environment should be SQL Server 2019 Enterprise edition.

You need to have the following installed on a dev. machine:

• VS2019, latest build
• SQL Server Integration Services Projects extention.

Answer 2

ORA-01005: null password given; logon denied.

ORA Error are always raise by Oracle, not by SSIS itself.
How do you set the password/it's encrytion? By default encrytion is by user key = your login. If you run the package by job, it's executed is Agent service account context <> your login.
Use an agent proxy with your account or encrypt the credentials in the package using a password instead.