In December 2014 I acquired a task to retrieve all the data from an application storing the data in a DBMS format known as DBISAM. DBISAM stores data for each table in separate files. I discovered that each table consisted of either two or three files, a .dat file with the data and a .idx file for indexes and optionally a .blb for blog storage of images or large text blocks.
The first, and just about only, place you can go on the Internet for things related to DBISAM is www.elevatesoft.com. You can download some free tools to assist with this task, and there are occasionally used forums where you can post questions. The staff responded quickly to my one post. From the downloads section you can download a package of tools called DBISAM-ADD. Get the most current version. The robust installer will place some programs on your computer (I was running Windows 8.1 and the tools worked fine).
The helpful tool is called “Database System Utility”, which is a program named dbsys.exe installed to C:\Program Files (x86)\DBISAM Additional\Version 4\utils\dbsys\win32. With this program you can select any table to open and run queries against it, including data modifications. You can also use the Export option to export the data to a .csv file. However, blob data, including Memo fields will not be included in the exported .csv file. So I do not recommend this option.
Getting the Data from Memo Fields Requires ODBC
ElevateSoft provides an ODBC driver on the same download page for the other DBISAM tools. After running the installer for the ODBC driver, you need to open “ODBC Data Source Administrator (32-bit)” and add a “System DSN”. I think you can use all the defaults if you are connecting to DBISAM files on your local computer. My DBISAM files all have a password to protect them, so I needed to enter that password on the last tab/prompt of the ODBC configure wizard. Then I was ready to use that ODBC driver with SSIS.
SSIS requires a standard edition or higher of SQL Server. You cannot use it with SQL Server Express Edition. Although I have installed a standard edition of SQL Server 2014, the most current SSIS tools are for SQL Server 2012. And even though they use Visual Studio, I don’t think you can open Visual Studio directly, or at least that has not worked for me. To get into SSIS, I open “SQL Server Data Tools for Visual Studio 2012”.
The secret to importing data using ODBC from DBISAM is to use the SSIS Import and Export Wizard. I could not figure out how to do this import without the wizard. Only the wizard allowed me to pick the ODBC connection I had created to the DBISAM Files. See the picture below for an example of the Source configuration for the wizard.
For the destination you can pick your SQL Server database, then you should be prompted with a list of tables to include. I had over 300 tables to import so I ran the wizard about 10 times and broke the 300 tables into smaller groups so that I could rerun the smaller chunks later.
The wizard does seem to have a problem with date and time fields coming from DBISAM and it will set their datatype to a number for mapping as in the screen shot below. Simply change that to a datetime, or smalldatetime if you prefer, and you should be ready to go.
If you plan to run this multiple times, which you probably want to write a script that runs before your packages to delete the tables you created if they currently exist. I recommend using the “Execute SQL Task” on the “Control Flow” tab of your package and having it run your “Data Flow Tasks” upon completion.
I also had some imports fail because the dates entered by the users into the DBISAM based system were outside the range of valid dates that SQL Server allows. I found data with dates like ’12/31/0210′ instead of ’12/31/2010′. I used the tools in the DBISAM utility to correct that data as shown here:
Select, “New Query” from the File menu and you can type in a query. Dates must be surrounded by single quotes and be in yyyy-mm-dd format. After correcting the data I was able to successfully import all the data.
Even the blob data such as images came across successfully into SQL Server blob datatypes.
Send me a message if you have any trouble with this.
Rob Kraft's Software Development Blog 