Thursday, October 21, 2010

Fishbowl (Firebird) ODBC Connection

My company decided to use the Fishbowl Point-of-sale system to handle bookstore activities. My project was to get transactions out of this system and imported into our SMS (Student Managment System) for which we use CampusVue.

Fishbowl's back-end is an open-source DBMS called Firebird. CampusVue is running on a SQL Server database.

As usual I decided to use SSIS to achieve this task.

The first challenge was to be able to connect to the bookstore database. Upon reviewing documentation of the Fishbowl product I was able to create an ODBC connection to it. You will need to obtain the credentials used if you are facing the same task. This particular credentials only allows reading of the data but that's all I need.

Here is the way the ODBC connection is set up for all who needs it.

32-bit vs 64-bit in SSIS

My recent project involved using a DSN as the connection string in SSIS. The challenge of the project was that I only had the 32-bit version of the driver to create the system DSN but the server was a 64-bit server. In Administrative tool there IS a 32-bit version of the ODBC editor so that was not a problem.

However, in order to make everything work in SSIS I had to do 2 things.

1) To debug in BIDS I had to change one of the solution properties. Under Debug set the Run64bitRuntime option to False.

2) In order to use the 32-bit runtime once the package was set to run as a SQL job in SSMS I had to check that option on the Step setup on the Execution Options tab.

After this everything was running smoothly.