Rob Kraft's Software Development Blog

Software Development Insights

How to move data from a DBISAM database into SQL Server

Posted by robkraft on December 15, 2014

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.

DBISAM Export

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.

Configure Data Source for DBISAM

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.

Edit Mappings for DBISAM

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:

Run A Data Modification Query in DBISAM

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.

Advertisements

8 Responses to “How to move data from a DBISAM database into SQL Server”

  1. Keith Pearce said

    Hi Rob
    I found your blog post when searching for ways to export a DBISAM 4 db into MSSQL server. I have followed the steps above but when i come to view the mappings it is blank, any idea what could be causing this?

    Many thanks

    Keith

    • robkraft said

      Hi Keith, I don’t know why that is happening. The wizard screens I go through are:
      1) Choose a Data Source (as shown above)
      2) Choose a Destination (and I am using SQL Server Native Client 11.0 and my server name and Windows Auth)
      3) Specific Table Copy or Query (and I am selecting the first bullet ‘Copy data…’)
      4) Then I get the Select Source Tables and Views dialog. I click on one to “check the checkbox”, then the “Edit mappings” button is enabled.
      5) When I open the “Column Mappings” dialog by clicking on the “edit mappings” button I see the mappings. I have the “Create destination table” Boolean selected, and nothing else.

      • Keith Pearce said

        Hi Rob
        Thanks for your reply. I think i have worked out the cause or possible causes, it’s either corrupt table headers or when the db was created it was done so using a different engine sig. So although it can read the tables and show a preview of the data it doesn’t seem to be able to extract the header information when doing mappings. I tried repairs etc on the database but nothing seems to work.
        Looks like I might have to go down the route of recreating the tables manually in SQL and then doing and export of the data to csv (which seems to work) and then import that.

        regards

        Keith

      • robkraft said

        Good luck Keith, but watch out for fields in the DBISAM files that are “Memo” fields, because I found those didn’t export to the .csv files for me.

  2. Kayden said

    Data in Memo type columns didn’t seem to copy over to MS SQL. What data type should I use in MS SQL for Memo type? I have tried with text or nvarchar(max) but no luck. Thanks

    • robkraft said

      Hello Kayden, I may not have had any memo data types in the data I brought in to SQLServer. nvarchar(max) should work though. If it fails, I suspect it is due to a special character in the source data such as a tab or carriage return. You probably need to clean the source data; remove whatever special characters are causing it to fail to copy, before you can import it into SQL Server.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: