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: [drain file 1 url] ([drain file 1 size])
Download Word: [drain file 2 url] ([drain file 2 size])
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?