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.


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.



By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.


  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?

    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.

  2. Do you have any examples on how you would use your ConversionUtility in a Oracle to SQL Server scenario?

    1. I don’t have one ready to go, but If you crack open ConversionUtility.cs in the repo, you would need to set it up instead of SSIS or Biml as the second and third column of the ConversionUtilty._privateMap, fill in the data types from Oracle. Then call the conversion utility to convert Oracle Data types to SQL and back again. In a fork of this code in use at an Oracle shop, I moved to a database backed conversion utility. That allowed me to have Many-many conversions. I stored rows for each direction of the conversion. From SQL to Oracle, from Oracle to SQL, and many more. In the end, this fork became a lot more useful than the hardcoded map I have in my repo.

  3. Hi Shannon,

    Thanks for your guide it helped me a lot.
    I am using the same connection as you do (ADO net), and it works fine. I just have one major issue. All the “date” data type from Oracle is translated into a string and then the component can’t run. When I create the manually it manages to translate the Oracle dates to SSIS dates, so it is only a problem using biml.

    1. I’ll bet it has something to do with how the dates are stored in Oracle. You may have to read them as strings, write them to staging as strings, then re-format them into dates with a complex string manipulation query. It’s ugly, but it’s the only way to ensure you get everything in staging, and then deal with formatting second.

      The fact you can get the correct results manually makes me think you could also have luck in “translating” the data types from Oracle. What I mean is, store the Oracle metadata to some structure (csv/sql/etc). Update the data types in that structure to reflect the data types from your manual version. Then use this “translated” metadata to build your packages.

Leave a comment

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