Everyone who knows me, knows I’m a Microsoft data platform professional. I do prefer their solutions over most other solutions. I know there have been a few times when I’ve shocked my coworkers when I’ve suggested Couch, Redis or some other NoSQL solution as a solution to a particular problem. The one that you’ve never heard me advocate for is Oracle. Some times I advocate that way based on the cost of the performance aspect. Other times, it’s based solely on the difficulty in setting up Windows development environments to connect with Oracle servers. I’m now following Microsoft’s current example: I want you to work with me, even if you aren’t using Microsoft products exclusively.
I’m working with a small team and a new client where Oracle sources are very important. So, I’m taking this time to update an article from Scott Currie on working with Oracle connections in BimlScript. The big difference in this article is the Oracle instance is hosted on my local network, and I’m using the Oracle Data Access Components (ODAC).
Installing Oracle Data Access Components
After downloading the components, installing them is a breeze. Extract the zip file, run setup. I did change the folders used to install the software. I put it all in c:\Oracle. Otherwise, I left the options set to their default. Most likely you will get an error about the length of your PATH environment variable. I chose “Ignore All”, and the install completed successfully.
Testing the Oracle Components
The quickest way to test if you’re good to go with the Oracle connection is to create a simple package. I created a package with two connections, one to my Oracle instance, and one to my SQL instance. I set up a data flow task to read data from one of my Oracle tables to a SQL table.
In defining the connections, you will notice I clicked “Test Connection” to make sure I could connect.
After setting up the test, I quickly ran it in debug mode. Everything worked as expected! After I knew I could connect, I wanted to go further with my demo.
Build Packages with Oracle Connections
Next, I wanted to build a package from Biml. A package that uses a connection to my Oracle instance. Defining connections to Oracle in Biml is similar to every other connection you’ve ever defined in Biml (lines 3-12). The difference here is the Provider. (line 10) I took the Provider attribute for my Oracle connection from the manual package I created in my previous test.
Simply right click on your Oracle connection in the package and choose properties. Then look at the Qualifier value. I take the whole value and copy and paste it into the Provider attribute in my BimlScript.
Once you have that defined, set up a Package with our two connections, and a data flow (lines 14 – 33). This script builds a simple data flow to move data from a single table on Oracle over to SQL Server. The only difference is, this time, the package is named Automatic, rather than Manual.
You could get as complex as you’d like in your example.
Interrogating Oracle Metadata
The last thing I wanted to test is interrogating the metadata from an Oracle source. This script is a second tier script. It expects that you’ve defined the Oracle connection “slowderalpha_Oracle” in tier 1. Starting on Line 9 I created a variable to hold the Oracle connection object. I then defined two lists, one to contain a list of Schemas and the other to contain a list of tables I’m interested in.
In Lines 14 – 19, I try different versions of the GetDatabaseSchema function from Biml. On line 15, you see I call GetDatabaseSchema with no arguments. In this case, I’ll get back all the source objects available via my Oracle connection and store them in my ImportResults variable. Sometimes you want to get everything from the source and pull it in.
On line 17, I wanted to just get the source objects that exist in the “HR” schema on the Oracle instance.
On line 19, I’m paring the list of included objects down to two tables, HR.Countries, and HR.Departments.
I ran all three scenarios to show the different ways you have to choose what objects to import.
Summary
Now that you know how to connect to Oracle with SSIS and Biml, you can quickly build out a staging solution in your environment. You can pick and choose what items to import, build destination objects, and then build packages to pump data from Oracle sources to SQL destinations. The only thing you may want is a translation from Oracle to SQL Server Datatypes. If you’re interested, I do have a framework for providing that translation: ConversionUtility.cs You will have to extend it so that it contains the Oracle mappings, but the framework is there.