Share via

Unable to successfully move data from an Oracle DB to a SSMS DB

Jacinta Ejiofor 0 Reputation points
2023-03-30T14:06:30.6466667+00:00

I am new to SSIS.

I am trying to move data from my Oracle DB to my SSMS DB using the SSIS tool.

The following are my environment details:

  • Microsoft Visual Studio Community 2019 Version 16.11.20
  • Microsoft SQL Server Integration Services Designer Version 16.0.5035.3
  • Server Management Studio Version 15.0.18384.0
  • ODBC Source is connected to a 32-bit ODBC connection manager

I have successful created both the ODBC and SSMS connection managers and can see data from the source and destination tables if I execute an SQL task directly to the DBs.

My challenge is that when I set up a data flow task with the following components, I encounter the errors below. Please how do I fix this error?
User's image

User's image

User's image

ERROR MESSAGE------------------------------------------------------------------------------------------------------

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [ODBC Source [2]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "test32BITS.JEJIOFOR200123" failed with error code 0xC0014009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error at Data Flow Task [SSIS.Pipeline]: ODBC Source failed validation and returned error code 0xC020801C.

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

Error at Package1 [Connection manager "test32BITS.JEJIOFOR200123"]: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

Best regards,

Jacinta.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,525 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
517 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 35,556 Reputation points
    2023-03-31T05:33:00.8333333+00:00

    Hi @Jacinta Ejiofor ,

    Please have check that Run64bitruntime property and set it to false for a try.

    User's image

    What's your version of the SQL Server?

    If you are using SQL Server 2019 CU1 and later, it is highly suggested that you may use Microsoft Connector for Oracle.

    he following Microsoft SQL Server products are supported by Microsoft Connector for Oracle:

    • Since SQL Server 2019 CU1
    • SQL Server Integration Services Projects for Visual Studio 2019

    Check: Microsoft Connector for Oracle

    Regards，

    Zoe Hui

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