VFP: Importing VFP data into SQL Server

V

I’ve written an article outlining How to use SQL Server 2005 Integration Services (SSIS) to import Visual FoxPro data into SQL Server.

The article is reasonably basic and covers:

  • Using SQL Server Business Intelligence Development Studio to create the package and manage errors
  • Using SQL Server Management Studio to create the Integration Service
  • Using SQL Agent job to schedule it
  • Gotchas (including UNC mapping, and the VFP OLE DB driver)
  • Brief overview of a real world implementation using SSIS
  • Brief discussion of SSIS versus VFP Upsizing Wizard

The article is 49 pages long, but don’t be alarmed, most of it is screen shots. It is available in Word or PDF format. I’ve included the Word format in case you want to edit the article, add your own comments, copy stuff out etc.

Download PDF: PDF-ImportingVFPintoSQLServerUsingSQLIntegrationServices.zip (6.79 MB)

Download Word: Word-ImportingVFPintoSQLServerUsingSQLIntegrationServices.zip (4.06 MB)

34 comments

  • Excellent work Craig!!

    We’ve done several VFP to SQL Server imports using SSIS and have done a handful of packages going in the other direction (SQL Server to VFP).

    I’m going to take a closer look soon and perhaps add some more thoughts…

  • Excellent work Craig!!We’ve done several VFP to SQL Server imports using SSIS and have done a handful of packages going in the other direction (SQL Server to VFP).I’m going to take a closer look soon and perhaps add some more thoughts…

  • Craig,

    I am very interested in your article but unfortunaly link does not work. Can you please fix the link or send to my e-mail?

    Thanks, Petr

  • Craig,

    I am very interested in your article but unfortunaly link does not work. Can you please fix the link or send to my e-mail?

    Thanks, Petr

  • I am very interested in your article but unfortunaly link does not work. Can you please fix the link or send to my e-mail?

    Danny

  • I am very interested in your article but unfortunaly link does not work. Can you please fix the link or send to my e-mail?

    Danny

  • I am interested in taking a look at the article, but the links are no longer working. Could you fix/send me a link to the article?
    Thanks
    Tom

  • I am interested in taking a look at the article, but the links are no longer working. Could you fix/send me a link to the article?
    Thanks
    Tom

  • NICE. Exactly what I needed. I was importing around 50 tables from some old FoxPro database into SQL Server 2008. I had to manually edit the package so I could make it work. Mainly, I used regex to replace some columns that weren’t auto-mapping correctly, and to add in the DROP TABLE statements. I didn’t want to do that for all of them! Thanks!
    John

  • NICE. Exactly what I needed. I was importing around 50 tables from some old FoxPro database into SQL Server 2008. I had to manually edit the package so I could make it work. Mainly, I used regex to replace some columns that weren’t auto-mapping correctly, and to add in the DROP TABLE statements. I didn’t want to do that for all of them! Thanks!
    John

  • I forgot to mention… SSIS did one weird thing when building the package: It split up the tables into blocks of four for the tasks!!! So I had like 20 tasks that would create 4 tables, then copy the data for those 4 tables. Is that normal? Can you change that? Is there a reason they did it this way?

    • Hmmm, strange – I hadn’t noticed that myself. Not sure why it would happen – but perhaps it’s for better performance (parallel processes?).
      (It’s a long time since I’ve done this, so my memory may be hazy).

  • I forgot to mention… SSIS did one weird thing when building the package: It split up the tables into blocks of four for the tasks!!! So I had like 20 tasks that would create 4 tables, then copy the data for those 4 tables. Is that normal? Can you change that? Is there a reason they did it this way?

    • Hmmm, strange – I hadn’t noticed that myself. Not sure why it would happen – but perhaps it’s for better performance (parallel processes?).
      (It’s a long time since I’ve done this, so my memory may be hazy).

  • Hi
    I am using SSIS package to import FoxPro table to SQL 2005 (I am using Microsoft OLE DB provider for FoxPro Sp2). All is fine except with memo field. My FoxPro memo field contains word document.
    When I export the contains of the memo field to SQL Image field all is ok but the data seem truncated.
    Because the data is truncated, it’s impossible for me to extract the word document from SQL Server Image Column using BCP tools.

    My dbf table have only 28 record, with one memo field, my .dbf file have 4KB and my .fpt file have 3039KB.
    I don’t Know what type of memo field is , but I can see word document format when I opening the .fpt file using notepad.
    Each document word don’t exceed some hundred of KB according to my FoxPro database client. for this I don’t understand why the document is truncated when is exported to SQL Server

    Thank for Help

    • Is it possible that the column mapping type is not correct? (page 21) – perhaps it’s been set as a char field instead of text?

  • Hi
    I am using SSIS package to import FoxPro table to SQL 2005 (I am using Microsoft OLE DB provider for FoxPro Sp2). All is fine except with memo field. My FoxPro memo field contains word document.
    When I export the contains of the memo field to SQL Image field all is ok but the data seem truncated.
    Because the data is truncated, it’s impossible for me to extract the word document from SQL Server Image Column using BCP tools.

    My dbf table have only 28 record, with one memo field, my .dbf file have 4KB and my .fpt file have 3039KB.
    I don’t Know what type of memo field is , but I can see word document format when I opening the .fpt file using notepad.
    Each document word don’t exceed some hundred of KB according to my FoxPro database client. for this I don’t understand why the document is truncated when is exported to SQL Server

    Thank for Help

    • Is it possible that the column mapping type is not correct? (page 21) – perhaps it’s been set as a char field instead of text?

  • This is great Craig, but what if I want to read data from FoxPro instead of moving the database files?

    We have a app that uses the FoxPro data, and I'd like to read this data in SQL to integrate it with other information already in SQL.

    example… the app stores user information, and I want to pull the current listing to compare to a list from PeopleSoft.

    Thanks Danette

  • Nice detailed post for this. I have a similar problem that Danette has. I only need to update an existing SQL-Server table with a handful of fields from a VFP table. This would be run weekly until the FoxPro app is rewritten in .NET.

    Thanks,

    Kraig

  • Hi,
    Thank you this is very helpful integration. I already followed that you suggestion. Then,i got error after run “Start Job at step” Please see as details log file.

    2554-03-12 15:30:35 – ? [393] Waiting for SQL Server to recover databases…
    2554-03-12 15:31:00 – ? [100] Microsoft SQLServerAgent version 9.00.5000.00 (x86 unicode retail build) : Process ID 3668
    2554-03-12 15:31:00 – ? [101] SQL Server HQ-CRM version 9.00.5000 (0 connection limit)
    2554-03-12 15:31:00 – ? [102] SQL Server ODBC driver version 9.00.5000
    2554-03-12 15:31:00 – ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
    2554-03-12 15:31:00 – ? [310] 2 processor(s) and 4086 MB RAM detected
    2554-03-12 15:31:00 – ? [339] Local computer is HQ-CRM running Windows NT 5.2 (3790) Service Pack 2
    2554-03-12 15:31:02 – ? [432] There are 11 subsystems in the subsystems cache
    2554-03-12 15:31:03 – ! [364] The Messenger service has not been started – NetSend notifications will not be sent
    2554-03-12 15:31:03 – ? [129] SQLSERVERAGENT starting under Windows NT service control
    2554-03-12 15:31:03 – + [260] Unable to start mail session (reason: No mail profile defined)
    2554-03-12 15:31:03 – + [396] An idle CPU condition has not been defined – OnIdle job schedules will have no effect
    2554-03-16 10:27:44 – + [000] Request to run job Product Import (from User HQ_DOMAIN\crmadmin) refused because the job has no job steps

  • that is great info in nutshell…
    one query:
    how to ignore logically deleted records in foxpro db from being migrated to SQL server database?

  • Thanks for the article… I know that this will help so much to my conversion from VFP tablest SQL Sever…
    I let you know if it works already… again, thanks in advance. From CDO,Phils.

  • Gracias por el Articulo…Yo tengo una base de datos muy grande que esta VFP 9 y necesito migrarlo a SQL con toda su estructura y data …ojala que este articulo sea de gran ayuda..soy nuevo en FOXPRO

  • Un consulta investigando encontré que se debe desistanlar los CTP Para la instalación del SEDNA.,,quisiera saber que es un CTP?..esperare su pronta respuesta gracias

  • Hello… Thanks for the step by step explaination.

    I was doing this for importing fox por tables into SQL Server 2008 R2.

    When I do this, I figured out that the import will also returned records marked as DELETED in the fox pro tables. I would like to know how can I avoid the deleted records which are appearing as normal records on my SQL server database.

    Any help would be appreciated.

    Thanks
    Farhan

    • Farhan, are you able to do a PACK and REINDEX on your FoxPro tables? This will remove all the deleted records from FoxPro, and then you can do the import into SQL. If you’d rather not remove them all, then you may need to copy to intermediate FoxPro tables, PACK and REINDEX those and then import.

  • Hi Craig – thanks for the doc. I’ve got a situation now where after running the SSIS package any blank date fields in my VFP database are now populated with 12/30/1899 in the new SQL database that is created by the package. Any way to stop that happening (I’d rather they were left blank during the import). Thanks!

    • Hi Steve. Sorry, it’s been a long time since I’ve worked with SQL and VFP, so I’m not in a position to really help with this. But leaving your comment here in case any other readers can chime in with thoughts.

About Craig

I'm the co-host of HubShots and the CEO of XEN - helping mid-large B2B companies with their digital marketing and lead generation.

Craig Bailey

Archives

Posts