Connect to Oracle database from SQL Server Integration Server
Introduction
The SQL Server Integration Service (SSIS) is an ETL (Extract Transform and Load) Tool from Microsoft for building enterprise-level data integration and transformation solutions.
It can be used for basic data integration and workflow applications but also can be used to perform broad data migration tasks. It features a data warehousing tool used for data extraction, transformation, and loading.
Prerequisites
Required Softwares:
- Microsoft Visual Studio (version 2014 or latest)
- SQL Server Data Tool (SSDT)
This tutorial assumes that you have ODBC drivers installed and a data source configured. If you have not yet done these steps, please follow these instructions. Ensure that Microsoft Visual Studio and ODBC Driver architecture matches. For example, if you have a 64-Bit Microsoft Visual Studio installed then you need to have the 64-Bit version of Progress DataDirect ODBC drivers installed. But most likely, your visual studio would be a 32-bit version and you would need a 32-bit driver.
1. Download and install the Progress DataDirect ODBC driver using the 15-day trial software. In this tutorial, we are connecting to Oracle Database.
2. Configure a data source for your database. See the DataDirect documentation for assistance with setting up your data source. You will learn how to configure an Oracle Data source in the following steps.
Configure Connection to Oracle ODBC Driver Using the ODBC Administrator
1. After the DataDirect Oracle ODBC driver has been installed, open ODBC Administrator to configure the connection and click on “Add”.
2. Choose the “DataDirect X.X Oracle Wire Protocol” and click on “Finish.”
3. The ODBC Oracle Wire Protocol Driver Setup window will pop up.
Enter your credentials, click on “Apply” and then click on “Test Connect.”
4. The Oracle Logon screen will pop up. Provide logon credentials and click on “OK.”
5. A dialog box will confirm that the connection is established.
Configure the DataDirect ODBC Oracle Driver with Microsoft Visual Studio.
1. Open Microsoft Visual Studio. You need to create a new SSIS project by going to File → New → Project shown in the below picture: -
2. A new window will appear, inside that select Integration Service Project (If not appearing, left side of the window go to Templates → Business Intelligence → Integration Services) as shown in the below picture: -
- Specify a suitable project Name and choose the desired location using Browse... on the lower side of the window, as shown in the above picture.
- Must ensure SSIS Toolbox is present on the left side of the window and Solution 'Integration Service Project' is present on the right side of the window. As shown in the below picture: -
- If the SSIS Toolbox does not appear on the left side of the window then, Right Click on Control Flow Page and select SSIS Toolbox. As shown in the below picture:-
3. From the left side of the window, Under SSIS Toolbox → Favorites → Data Flow Task, drag and drop the Data Flow Task into Package.dtsx[Design]* Page section. As shown in the below picture: -
4. Double-click on the Data Flow Task, and you must be automatically moved to Data Flow Page from Control Flow Page, and now the Data Flow Page is blank.
- Now, you need to choose/select your data source type from the left side of the window under SSIS Toolbox → Source, Drag and Drop desired data source into the Data Flow Page.
- Now, you need to establish a connection from that desired data source for that, below the Data Flow Page Under Connection Managers Section Right-click and select New Connection from the appeared menu list. As shown in the below picture: -
5. A new window will appear in front of you, select the desired Connection Manager and Click on Add..., As shown in the below picture: -
Here we are connecting to Oracle ODBC so we add ODBC Connection Manager.
6. We are creating a new connection manager so click on “New” and then click on “Ok”.
7. A new pop-up will appear in which select the “Use connection string” radial button and click on “Build”.
8. Go to Machine data source and select your Data Source and click “Ok”.
9. Give the required logon credentials and click “Ok”.
10. A connection string is built in the connection manager and now you can click on “Ok”.
11. Connection Manager is created, and you can see your Data source in the data connections. Select it and click on “Ok”.
12. After successfully connecting to the source DSN, you can preview the source data by clicking on Preview..., a popup will appear having the title Preview Query Results. As shown in the below picture: -
13. Now, you need to choose/select the desired destination type from the left side of the window under SSIS Toolbox → Destinations, Drag and Drop desired data destination into the Data Flow Page, similarly to the data source.
14. Now, you need to connect the ODBC Source and ODBC Destination using the mouse pointer, Just move the mouse cursor over the ODBC Source. A blue color arrow will appear just expand it and connect to the ODBC Destination.
15. Similarly, you need to configure the destination also. Just double-click on ODBC Destination and a window will appear having the title ODBC Destination. Just check the ODBC Connection Manager drop-down list to see if your desired connection is present or not. If not, just click on New... The available ODBC connection will show just select it. Also, select the table and click “OK”.
16. After this, you need to map the columns between the ODBC Source Table to the ODBC Destination Table by clicking on the Mappings Tab, available on the left side of the window and just below the Connection Manager.
- Map the columns as one-to-one, as per requirement, and must ensure the data type of the mapping columns is the same.
17. Well done, All set. Now, you need to click Start. The data transfer process has started. You can see the progress by clicking Progress. As shown in the below picture: -
18. Finally, the data is transferred from the ODBC Source table to the ODBC Destination table.
Conclusion
Get started today with a free 15-day trial of Progress DataDirect Oracle ODBC drivers, and connect to Microsoft SQL Server SSIS.
Contact Us for assistance with any questions you may have, and we will be happy to help!