This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(166.4, 88%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EPG%3C/text%3E%3C/svg%3E)
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.
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.
In 2008, I am using Native OLE DB \ Oracle DB driver. But the same is not present in 2017
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 74%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EL2%3C/text%3E%3C/svg%3E)
In SQL Server Integration Services (SSIS) 2017, there are several features related to databases that you may find useful. One important feature is the ability to deploy packages to the SSIS Catalog database (SSISDB) on Azure SQL Database. With this feature, you can run packages deployed to Azure on the Azure-SSIS Integration service. This allows for more flexibility and scalability in your SSIS workflows
Another key aspect is the SSIS Catalog database (SSISDB) itself. The SSISDB is a dedicated database that can be used to deploy and manage SSIS projects. It provides a centralized location to store and organize SSIS packages, configurations, and environments. You can use the SSISDB to deploy, validate, execute, and monitor your SSIS projects. It offers features such as package execution history, event handling, and project versioning 2.
Additionally, when working with databases in SSIS 2017, it is important to ensure that you have the necessary connectivity. For example, if you need to connect to an Access .accdb file, you can use the SSIS provider to establish the connection. It is worth noting that configuring the connection and accessing the database may require specific steps and considerations, such as setting up the appropriate connection manager and selecting the correct provider.3
These features and considerations can help you effectively work with databases in SQL Server Integration Services 2017.
To install SQL Server Integration Services in Visual Studio 2019, you can follow a step-by-step process. The detailed instructions can be found in an article on MSSQLTips <sup>3^</sup>. This article explains how to install Integration Services in Visual Studio 2019 and provides a history of the tool.
In summary, to work with SQL Server Integration Services projects on Windows Server 2019 with SQL Server, you need to install the SQL Server Integration Services Projects and then create and manage your SSIS packages within the project.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(32, 38%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJN%3C/text%3E%3C/svg%3E)
Use OLE DB provider for connecting SSIS.
On Connection Manager page, Oracle Source Editor dialog box is to select Oracle Database as source, table, or view from the database.
Open the SQL Server Integration Services (SSIS) package that has the Oracle source.
On the Data Flow tab, double-click the Oracle source.
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.
Hi @ZoeHui-MSFT & @Olaf Helper
I tried the way Olaf mentioned but no success.
@ZoeHui-MSFT , I got another old thread,
https://learn.microsoft.com/en-us/answers/questions/1086085/microsoft-ole-db-provider-for-oracle-not-working
I am facing exact same issue when trying the way @Olaf Helper suggested.
I have following system configurations:
Windows Server 2019
Visual Studio : 2017
SSDT : 2017
19c Oracle Client: 64 bit
SQL Server: 2014 Developer Edition
As I understand this will not work with 32 bit Visual Studio.
Can you please help with which exact 32 bit drivers are needed to connect to oracle.
After researching, on some resources I got Attunity drivers are required. On some resources I found to install Microsoft Connector for Oracle V1.1
Can you please help me with this.
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:
The following Oracle database versions of data source are supported:
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
Hi @ZoeHui-MSFT,
Microsoft Connector for Oracle has two big limitations: