Importing from dBase or other DBF files

Microsoft in the News

Sometimes the cure is worse than the disease.  The recent attempts by Microsoft to fix the problems built into AMD and Intel chips is a good example.

By now, you have probably heard of the security threats called Meltdown and Spectre.  They attacks flaws built into Intel and AMD microchips.  Clearly, we aren’t all going to replace the chips in our computers.  Even if we were willing or able to do so, it may be a long time before the hardware fix for these security threats are available.

The threat has been known for about a year now, but only recently was it made public via a Google blog site.  When these flaws are exploited, things like passwords that are stored in the memory of running programs can be extracted.  This can be done on PC’s, Mac’s, mobile devices, and in the cloud.

For a description of how these threats work, see:  https://meltdownattack.com/

With a serious flaw like this, a software fix has to be rolled out in a hurry.  Everyone knew that the fix was going to impact computer performance.  But Microsoft’s cure seems to have killed some patients.  Some AMD chip machines running Windows 10 have been bricked as a result of installing mandatory Windows updates.

As of January 10, Microsoft has pulled the potentially deadly cure.

Importing from dBase or other DBF files

I was recently tasked with the job of importing some dbase files into SQL.  As usual, I get the work because it is not a straight forward task and so it requires a bit more specialized knowledge.  In figuring out the issues, I thought others would benefit from this experience.

The following link is where Microsoft lets us know that the import wizard in SQL does not support imports from dBase or other DBF files.

https://docs.microsoft.com/en-us/sql/integration-services/connection-manager/connect-to-a-dbase-or-other-dbf-file

And hence, I was called.

The key to making this work is to:

1. Ensure you are using the correct drivers, and
2. Set up SSIS correctly from the start.

So, step one, the driver you need can be found here: https://www.microsoft.com/en-us/download/details.aspx?id=14839.  Download and install.

Step two, we set up the SSIS.  I created all the tables to make this easier.

To create the control flow of the packages, do the following:

1. To add a data flow task to the Package, drag and drop it on to the control flow
2. Right click rename to change the name of the data flow task
3. Double click on the data flow task to edit it
4. Edit takes you to the data flow tab
5. For each file you want to import, do the following;
   1. Drag in the source assistant
      1. Choose New in the Select connection managers
      2. In the Provider option at the top of the connection Manager choose;
         1. OLE DB Provider: Microsoft OLD DB Provider for Visual FoxPro
         2. Server or file name: \\lvapps\datasources\ExportData\QAExports\filename.dbf
         3. Test Connection
         4. Ok
         5. Double click the OLE DB Source
         6. Choose the name of the table or view from the list in the drop down
         7. In the properties Tab be sure to change the field AlwaysUseDefaultCodePage to True as below.

If you forget the above step you get this error:

Now add the Destination information

1. Drag in the Destination Assistant
   1. Choose the <instancename>\<Database>.<table> Connection
   2. Dag and drop the Blue arrow to connect the source to the destination.

• Double click to open the OLE DB Destination

1. Choose the name of the table or the view to want to load from the drop down.
2. Choose ok.

To add to the complication of this import, issues crop up if you are running a 64 bit version of SQL.  I’ll deal with that obstacle in my next blog.