Biml and Oracle Connections

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 CouchRedis 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.

 

 

3 Comments on "Biml and Oracle Connections"



  1. Hi Shannon,

    How do you find the performance of the ADO.Net connections compared with the Microsoft Connectors from Attunity and were you able to use those within your Biml?

    Reply

    1. In querying the metadata, it’s as fast as getting it through Toad. Now, for getting bulk data from Oracle, I’ve noticed it is a bit slower. I should work with someone to set up a decent database in SQL Server and Oracle, and then let Biml build the packages to stage that data to the same destination ( so Oracle to Oracle and SQL to SQL). The measure the times using ADO.Net connections and Attunity connections. Then, test cross-database data staging (Oracle to SQL and vice-versa). I’d be interested in the results. I always prefer it’s x faster/slower to… it seems slower.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *