Creating a SSIS package to get data from free DBF (Visual FoxPro table) in 64 bit SQL Server

Question

Hi everybody,

I originally posted in the wrong forum MSDN Thread

I'm trying to create (for test purposes) a package to load data from VFP table into SQL Server 64 bit. So far I can not figure out how to do it - I created DSN as outlined in Todd's blog, but I don't know what to do next.

Can someone please give me step by step instructions so I will know if it's possible or not? I was under impression it's not possible, but Todd's blog seems to suggest otherwise.

Thanks in advance.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 1

Hi everybody,

I was pointed today to this very helpful blog post Quick Reference: SSIS in 32- and 64-bits

I've created the User DSN pointing to VFP free table using odbcad32 from SysWOW64 folder.

Now I opened BIDS, started a new SSIS project, added Data Flow task, created Connection Manager using this DSN and now I'm stuck, as I don't know what do to next.

Thanks a lot in advance.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 2

The easiest way to start is the SSIS Import/Export Wizard.  Object Explorer, right click database, tasks, import data.

You can save the generated SSIS package and enhance it later.

---

Kalman Toth SQL SERVER & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Answer 3

Kalman,

In the SSIS Import/Export Wizard I don't see an ability to select User DSN as the data source. I tried before selecting VFP Ole DB and I was getting all sorts of errors, so I'm not even going to try it today - I know it will not work.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 4

Can you try: ".Net Framework Data Provider for Odbc"

Related link: http://www.sqldev.org/sql-server-integration-services/ssis-import-export-wizard-data-sources-1338.shtml

---

Kalman Toth
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Answer 5

I've tried it. When I select a table - the corresponding table seems to create automatically, I'm getting this error on the Mapping step

===================================

TITLE: SQL Server Import and Export Wizard

------------------------------

 

Column information for the source and destination data could not be retrieved.

 

 

"projbuild" -> [dbo].[projbuild]:

 

     - Cannot find column -1.

 

 

------------------------------

ADDITIONAL INFORMATION:

 

Cannot find column -1. (System.Data)

 

------------------------------

BUTTONS:

 

OK

------------------------------

And the technical details are:

Column information for the source and destination data could not be retrieved.

 

 

"projbuild" -> [dbo].[projbuild]:

 

     - Cannot find column -1.

 (SQL Server Import and Export Wizard)

 

===================================

 

Cannot find column -1. (System.Data)

 

------------------------------

Program Location:

 

   at System.Data.DataColumnCollection.get_Item(Int32 index)

   at Microsoft.DataTransformationServices.Controls.ProviderInfos.MetadataLoader.LoadColumnsFromTable(IDbConnection myConnection, String[] strRestrictions)

   at Microsoft.SqlServer.Dts.DtsWizard.OLEDBHelpers.LoadColumnsFromTable(MetadataLoader metadataLoader, IDbConnection myConnection, String[] strRestrictions, DataSourceInfo dsi)

   at Microsoft.SqlServer.Dts.DtsWizard.TransformInfo.PopulateDbSourceColumnInfoFromDB(IDbConnection mySourceConnection)

   at Microsoft.SqlServer.Dts.DtsWizard.TransformInfo.PopulateDbSourceColumnInfo(IDbConnection mySourceConnection, ColumnInfoCollection& sourceColInfos)

-----------------------

So, not sure what to do - this is when trying using Import Wizard.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 6

There are TWO places you can change column behaviour/mapping:

1. Advanced Tab on Data Source page:

   o Rename Column 0 --> (something)

   o Change column data type /size

2. Select Source Tables and Views

  o Edit mappings

  o Edit SQL

Use the View options to see the source data looks OK prior to releasing/saving the package.

---

Kalman Toth SQL SERVER & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Answer 7

I have no idea what you've tried, but assuming that all you've done is to create a DSN, I would do the following:

1. Create a new SSIS Package 
2. Add a Data Flow Task.
3. In the Data Flow task, 

• add an ADO .Net connection manager. In the provider, use ODBC. Set this up to point to your DSN
• Add a OLEDB connection manager for your SQL Server.
• Add a ADO.Net source and connect it to your ADO .Net connection manager. Select your table, or specify a query.
• Add a OLEDB Destination. Drag the data flow path from the source to the destination.
• set the destination table

I've left out some details, but that should work

---

Craig Bryden - Please mark correct answers

Answer 8

Hi Naom,

In order to load data from Visual FoxPro to 64-bit SQL Server, we can use Microsoft OLE DB Provider for Visual FoxPro or Visual FoxPro ODBC Driver to load data from Visual FoxPro, and then upload into SQL Server.

Below are the detail steps for your reference:
Before starting the steps, please make sure the Microsoft OLE DB Provider for Visual FoxPro and Visual FoxPro ODBC Driver is installed.
Solution1: Use the Microsoft OLE DB Provider for Visual FoxPro to load data from Visual FoxPro database

1. Create a new SQL Server Integration Services(SSIS) package.
2. Add a Data Flow Task(DFT) to the package.
3. Select the DFT, and go to Data Flow designer page.
4. Add a OLE DB Source to the package.
5. Double-click the OLE DB Source, click "New" > "New" to create a connection to Visual FoxPro.
6. In the Connection Manager, please select "Microsoft OLE DB Provider for Visual FoxPro"
7. Type the file path of a Visual FoxPro database(e.g. C:\Address.dbc).
8. Test the connection by clicking button "Test Connection"
9. Click "OK" > "OK" to apply.
10. Now, we can select a table from the Visual FoxPro database in the OLE DB Source editor.

Solution2: Use the Visual FoxPro ODBC Driver to load data from Visual FoxPro database

1. As you done, create a DSN pointing to VFP free table using odbcad32 from SysWOW64 folder. I would suggest you using System DSN instead of User DSN.
2. Create a new SQL Server Integration Services(SSIS) package.
3. Add a Data Flow Task(DFT) to the package.
4. Select the DFT, and go to Data Flow designer page.
5. Add an ADO NET Souce to the package. Please note, ADO NET is started to support from SSIS 2008. In other words, in SSIS 2005, there is no ADO NET source.
6. Double-click the OLE DB Source, click "New" > "New" to create a connection to Visual FoxPro.
7. In the Connection Manager, please select "Odbc Data Provider"
8. Select the DSN we created before, and then test the connection, make sure it is successful.
9. Click "OK" > "OK" to apply.
10. Now, we can select a table from the Visual FoxPro database in the ADO NET Source editor. If it is fail, please try using SQL Command mode.

Additionally, we strongly recommend using the Visual FoxPro OLE DB provider as a replacement. Also, in design-time, pleaes set the package to run in 32-bit mode, otherwise the package will fail to run.
Right-click the project > "Properties" > "Debugging" > Set "Run64Runtime" to be "False".

The two drivers can be downloaded from:
http://www.microsoft.com/downloads/en/details.aspx?familyid=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&displaylang=en
http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx

If you have any more questions, please feel free to ask.

Thanks,
Jin Chen

 

---

Jin Chen - MSFT

Answer 9

I seemed to be stuck at this step Add a ADO.Net source and connect it to your ADO .Net connection manager. Select your table, or specify a query.

I'm not sure how exactly can I do this. I added ADO.NET source, but how to connect to ADO.NET connection manager is not clear. Wish you can show or somehow Team Viewer with me.

Thanks again.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 10

Just dbl-click the source. It will bring the properties up. The first selection is the connection manager. You then select the Data Access mode and either the table name or your query

Unfortunately, I'm on a government contract right now and my machine is severely locked down. I cannot use any remote assistance tools

 

---

Craig Bryden - Please mark correct answers

Answer 11

Hi Naom,

Are you still there? If so, please join 147 472 328, 5898

Thanks,
Jin Chen

---

Jin Chen - MSFT

Answer 12

Jin,

I'm sorry, your last detailed solution worked great for me. I haven't tried creating a job yet to run this package (I was testing it anyway), but using the steps you provided worked fine for 1 free VFP table. It's late today, so I may resume more tests tomorrow, but I'm very grateful to you and Craig for the help.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 13

Hi Jin ,

when I use OLEDB for Foxpro , I got this error : cannot retrieve the column code page info from the OLEDB provider .

and I cannot create DSN pointing to Fox pro because in my ODBC , in the list , I just have sql server drive.

I already installed VFP odbc and OLEDB . I have many problems with working with Fox pro , when I want to run a query in report builder or BI and I have datatime function or boolean datatype ,all the time I got an error that said this function doesn't exist.

I'd appriciate your help .

 

regards,

Marzi

 

Answer 14

Are you doing it from the BIDS? Using the steps outlined in the first part of Jin's message worked fine for me. Perhaps because my tables don't use non-standard codepage.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 15

Hi Naom

I have issues in BI and report builder . when I run a query in report builder ,it is fine in local machine but when i connet to report server , there is an error that says VFPOLEDB.1 doesn't exist .

another problem which i have , in report builder,query builder , I cannot use functions for datetime and othere things , it says :Trunc.prg or To_date.prg ,etc  done't exist in VFP driver .

i should work with fox pro and there is no way for me :(

please help me

thanks

Marzi

 

Answer 16

What is your query and do you have VFPOleDB installed on the server? This is where you can get it from VFPOleDB Download

You can not use functions that VFPOleDB doesn't understand such as trunc or to_date. They are ORACLE functions, as far as I know. You need to use VFP functions when working with VFP data.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 17

yes , I used VFP functions and it works . thank you :)

but still i have a problem in running reports in report server . acctually when i am not connected to report server , i can run reports with embeded data sources . but when i connect to report server , in query designer , i can see the result of query but when i want to run the report , it doesn't work and says :The 'VFPOLEDB.1' provider is not registered on the local machine.

 

what do you think of this problem ?

 

i'd really appriciate your help.

 

Marzi

 

Answer 18

Well, the error message suggests that the local computer does not have VFPOleDB installed. I think you need to uninstall VFPOleDB on that computer and re-install it and then re-try.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 19

Hi Naomi,

I uninstalled and again installed but again same error :The 'VFPOLEDB.1' provider is not registered on the local machine.

:((((

:(''''

once I disconnet report server in report builder , it works properly .

I tried to make some sample reports with advanture database , it worked properly in report server .I have problem with VFPOLEDB .

do you have any advice ?

thanks

Marzi

 

Answer 20

the error which i get everytime in reportserver link :

can you please help me?

• An error has occurred during report processing. (rsProcessingAborted)
  • Cannot create a connection to data source 'quids'. (rsErrorOpeningConnection)
    • The 'VFPOLEDB.1' provider is not registered on the local machine.

Answer 21

I'm afraid you're already ahead of me as I haven't tried running reports against VFP data. Not sure if this is of any help, but here is an interesting article (in Russian) about incorporating Reporting Services into VFP application

Reporting Services in VFP

I suggest to start a new thread is SSRS forum. If you do, can you post a link? Thanks.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 22

Hi Naom,

In order to load data from Visual FoxPro to 64-bit SQL Server, we can use Microsoft OLE DB Provider for Visual FoxPro or Visual FoxPro ODBC Driver to load data from Visual FoxPro, and then upload into SQL Server.

Below are the detail steps for your reference:
Before starting the steps, please make sure the Microsoft OLE DB Provider for Visual FoxPro and Visual FoxPro ODBC Driver is installed.
Solution1: Use the Microsoft OLE DB Provider for Visual FoxPro to load data from Visual FoxPro database

1. Create a new SQL Server Integration Services(SSIS) package.
2. Add a Data Flow Task(DFT) to the package.
3. Select the DFT, and go to Data Flow designer page.
4. Add a OLE DB Source to the package.
5. Double-click the OLE DB Source, click "New" > "New" to create a connection to Visual FoxPro.
6. In the Connection Manager, please select "Microsoft OLE DB Provider for Visual FoxPro"
7. Type the file path of a Visual FoxPro database(e.g. C:\Address.dbc).
8. Test the connection by clicking button "Test Connection"
9. Click "OK" > "OK" to apply.
10. Now, we can select a table from the Visual FoxPro database in the OLE DB Source editor.

Solution2: Use the Visual FoxPro ODBC Driver to load data from Visual FoxPro database

1. As you done, create a DSN pointing to VFP free table using odbcad32 from SysWOW64 folder. I would suggest you using System DSN instead of User DSN.
2. Create a new SQL Server Integration Services(SSIS) package.
3. Add a Data Flow Task(DFT) to the package.
4. Select the DFT, and go to Data Flow designer page.
5. Add an ADO NET Souce to the package. Please note, ADO NET is started to support from SSIS 2008. In other words, in SSIS 2005, there is no ADO NET source.
6. Double-click the OLE DB Source, click "New" > "New" to create a connection to Visual FoxPro.
7. In the Connection Manager, please select "Odbc Data Provider"
8. Select the DSN we created before, and then test the connection, make sure it is successful.
9. Click "OK" > "OK" to apply.
10. Now, we can select a table from the Visual FoxPro database in the ADO NET Source editor. If it is fail, please try using SQL Command mode.

Additionally, we strongly recommend using the Visual FoxPro OLE DB provider as a replacement. Also, in design-time, pleaes set the package to run in 32-bit mode, otherwise the package will fail to run.
Right-click the project > "Properties" > "Debugging" > Set "Run64Runtime" to be "False".

The two drivers can be downloaded from:
http://www.microsoft.com/downloads/en/details.aspx?familyid=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&displaylang=en
http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx

If you have any more questions, please feel free to ask.

Thanks,
Jin Chen

 

---

Jin Chen - MSFT

Hi Chen,

My SSIS package ran Ok within VS 2008 with ODBC for foxpro...but when i create a job for it, failed, error:

Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  10:32:34 AM  Error: 2010-12-01 10:32:54.00     Code: 0xC0047062     Source: Data Flow Task ADO NET Source [264]     Description: System.Data.Odbc.OdbcException: ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Cannot open file \\server11\data\mydata.dbc.  ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed  ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).     at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)     at ....

Please help...

Answer 23

Hi Marzi,

What is the bit version of your SQL Server Reporting Services(SSRS)?
Please note the VFP driver is only supported 32-bit application, if the SSRS is 64-bit, the report will fail too. The workaround please see my reply in Naom's previous thread.

Thanks,
Jin Chen

---

Jin Chen - MSFT

Answer 24

Hi Hoang1,

Is the DSN a user DSN? If so, please try to create a System DSN instead.

Thanks,
Jin Chen

---

Jin Chen - MSFT

Answer 25

Hi Hoang1,

Is the DSN a user DSN? If so, please try to create a System DSN instead.

Thanks,
Jin Chen

---

Jin Chen - MSFT

Hi Chen, I did a SYSTEM DSN and still have the error:

Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  10:32:34 AM  Error: 2010-12-01 10:32:54.00     Code: 0xC0047062     Source: Data Flow Task ADO NET Source [264]     Description: System.Data.Odbc.OdbcException: ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Cannot open file \\server11\data\mydata.dbc.  ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed  ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).     at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)     at ....

Please advice...Thanks Chen