Art of BI: Moving ODBC DSNs from source machine to target – more fun with Windows

By | In 11g, Art of BI | August 09th, 2012

Every now and then comes the need to work on a Windows Server.  With OBIEE this is some times a gift and a curse.  Either way, if using multiple data source systems for connectivity you are most likely using the ODBC Administration tool.  And what happens when you have a slue of DSN’s configured and you need to move them from your “Development” machine to the subsequent envionrment, let’s say “QA/TEST”?

The easiest manual way to do this is to copy the regiestry files from one machine to another. I won’t reinvent the wheel here but just point you in the right direction as well as to create a reference which I’ve used more than once at this point.

The basics are explained, here:

The main caveat here is that because a unique BI Server DSN ODBC entry (ex: coreapplication_OHSO30998023A, etc.) gets created with each BI Server installation, you cannot just copy the entire directory and expect things to work on the target server, without a bit of extra TLC.

To work around this you can do two things:

– Export the entire ODBC.INI folder and import it to the target system per the instructions from John Savill in the link above.  Once that is accomplished, reference the backup ODBC.INI.BAK (or whatever you named it) and edit the registry string value for the coreapplication_OHS<unique_string> odbc entry as you have just copied the one from your source environment and they won’t be the same.  You’ll also need to edit the key/value pairs inside of that folder by changing the source’s server name and port information to that of the target’s.

– The other way to go about this is to export each individual DSN folder to its own REG file.  Only copy the entries that don’t yet exist in the target. So by cherry picking the entries you can avoid select the source environments coreapplication_OHS<unique_string> ODBC connection.  You will also need to export the values inside of the “ODBC Data Sources” folder.  Import each of these REG files into the target machine.  Once you have imported the ODBC.INI files then import the “ODBC Data Source” file. You will then see two entries prefixed with “coreapplication”. Reference the backup ODBC.INI folder and confirm which one should actual remain; delete the other reference which comes from the source system.

You will still need to verify that each of the connections works properly.  Some ODBC connections such as those for SQL Server, etc. that are not using the default ports may revert to the defaults which will require you to manually update its configuration once moved to the target machine. This is a good practice to verify after the migration anyway.

Good luck with the Windows OS joy ride.

Contact Us
Christian Screen
Christian is an innovator in analytics and data warehousing design, best practices, and delivery. With more than fifteenyears of decision support and data warehousing with key experiences at Office Depot HQ, Sierra-Cedar, and Capgemini, he oversees the Oracle Analytics Practice which includes the technical development and delivery of Oracle BI collaboration software, data warehouse solutions, Oracle BI/EPM projects, and packaged analytics solutions at Datavail.

Leave a Reply

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

1 thought on “Art of BI: Moving ODBC DSNs from source machine to target – more fun with Windows”