How to connect to Oracle database in SSIS 2017 and use the connection in execute Sql task

Pooja Gogia 20 Reputation points
2023-08-29T07:25:00.21+00:00

I have an execute SQL task (which calls a stored procedure) with Oracle DB Connection via Native OLE DB\Oracle provider for ole db in SSIS 2008 version. Now, I have to migrate the package to 2017 version. Can someone help me with how to connect to Oracle DB in SSIS 2017 and alternate to execute sql task to call my stored procedure.

Windows Server 2019
Windows Server 2019
A Microsoft server operating system that supports enterprise-level management updated to data storage.
3,589 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,277 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,510 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 42,926 Reputation points
    2023-08-29T10:28:48.4966667+00:00

    Can someone help me with how to connect to Oracle DB in SSIS 2017

    It's in 2017 the same as in 2008; create a connection the same way as before.

    2 people found this answer helpful.

4 additional answers

Sort by: Most helpful
  1. Jadav Netra Piyush 0 Reputation points
    2023-08-29T10:00:02.4366667+00:00
    1. Add the Components. To get started, add a new Oracle source and SQL Server ADO.NET destination to a new data flow task.
    2. Create a New Connection Manager. Follow the steps below to save Oracle connection properties in a connection manager.
    3. Configure the Oracle Source
    4. Configure the SQL Server Destination.
    5. Run the Project.

    Use OLE DB provider for connecting SSIS.


  2. ZoeHui-MSFT 34,996 Reputation points
    2023-08-30T01:32:19.65+00:00

    Hi @Pooja Gogia,

    Like Olaf mentioned that you may set the Oracle DB Connection via Native OLE DB\Oracle provider for ole db in SSIS 2017 version like SSIS2008.

    Here is a detailed tutorial about setting execute sql task.

    Check it out here: How to Execute Stored Procedure in SSIS Execute SQL Task in SSIS

    Regards,

    Zoe Hui


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


  3. ZoeHui-MSFT 34,996 Reputation points
    2023-08-31T09:44:32.16+00:00

    Hi @Pooja Gogia,

    Microsoft Connector for Oracle enables the ability to export data from and load data into Oracle data source in an SSIS package. However, it has the limitation like shown below.

    Version support

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

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

    The following Oracle database versions of data source are supported:

    • Oracle 10.x
    • Oracle 11.x
    • Oracle 12c
    • Oracle 18c (without Windows Authentication support)
    • Oracle 19c (without Windows Authentication support)

    The Oracle database is supported on all operating systems and platforms.

    Check this thread to see if it is helpful.

    Regards,

    Zoe Hui


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


  4. Pooja Gogia 20 Reputation points
    2023-09-08T13:31:33.3066667+00:00

    Thanks @Olaf Helper & for your help. My issue got solved.

    Regards,

    Pooja Gogia

    0 comments No comments