Skip to content

shannonlowder.com

Menu
  • About
  • Biml Interrogator Demo
  • Latest Posts
Menu

Biml and Oracle Connections

Posted on September 13, 2017September 5, 2017 by slowder

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.

 

 

Leave a Reply Cancel reply

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

Recent Posts

  • A New File Interrogator
  • Using Generative AI in Data Engineering
  • Getting started with Microsoft Fabric
  • Docker-based Spark
  • Network Infrastructure Updates

Recent Comments

  1. slowder on Data Engineering for Databricks
  2. Alex Ott on Data Engineering for Databricks

Archives

  • July 2023
  • June 2023
  • March 2023
  • February 2023
  • January 2023
  • December 2022
  • November 2022
  • October 2022
  • October 2018
  • August 2018
  • May 2018
  • February 2018
  • January 2018
  • November 2017
  • October 2017
  • September 2017
  • August 2017
  • June 2017
  • March 2017
  • February 2014
  • January 2014
  • December 2013
  • November 2013
  • October 2013
  • August 2013
  • July 2013
  • June 2013
  • February 2013
  • January 2013
  • August 2012
  • June 2012
  • May 2012
  • April 2012
  • March 2012
  • February 2012
  • January 2012
  • December 2011
  • November 2011
  • October 2011
  • September 2011
  • August 2011
  • July 2011
  • June 2011
  • May 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • October 2010
  • September 2010
  • August 2010
  • July 2010
  • June 2010
  • May 2010
  • April 2010
  • March 2010
  • January 2010
  • December 2009
  • November 2009
  • October 2009
  • September 2009
  • August 2009
  • July 2009
  • June 2009
  • May 2009
  • April 2009
  • March 2009
  • February 2009
  • January 2009
  • December 2008
  • November 2008
  • October 2008
  • September 2008
  • August 2008
  • July 2008
  • June 2008
  • May 2008
  • April 2008
  • March 2008
  • February 2008
  • January 2008
  • November 2007
  • October 2007
  • September 2007
  • August 2007
  • July 2007
  • June 2007
  • May 2007
  • April 2007
  • March 2007
  • February 2007
  • January 2007
  • December 2006
  • November 2006
  • October 2006
  • September 2006
  • August 2006
  • July 2006
  • June 2006
  • May 2006
  • April 2006
  • March 2006
  • February 2006
  • January 2006
  • December 2005
  • November 2005
  • October 2005
  • September 2005
  • August 2005
  • July 2005
  • June 2005
  • May 2005
  • April 2005
  • March 2005
  • February 2005
  • January 2005
  • November 2004
  • September 2004
  • August 2004
  • July 2004
  • April 2004
  • March 2004
  • June 2002

Categories

  • Career Development
  • Data Engineering
  • Data Science
  • Infrastructure
  • Microsoft SQL
  • Modern Data Estate
  • Personal
  • Random Technology
  • uncategorized
© 2025 shannonlowder.com | Powered by Minimalist Blog WordPress Theme