UPDATE
Turned out I was wrong and it’s easy enough to create SSIS package to run an import of VFP table into SQL Server 64 bit. The complete solution was provided by Jin Chen in this MSDN thread How to run SSIS package – Import VFP table to SQL Server 64 bit and I was able to easily follow it and the package ran OK. I now quote the solution:
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
Create a new SQL Server Integration Services(SSIS) package.
Add a Data Flow Task(DFT) to the package.
Select the DFT, and go to Data Flow designer page.
Add a OLE DB Source to the package.
Double-click the OLE DB Source, click “New” > “New” to create a connection to Visual FoxPro.
In the Connection Manager, please select “Microsoft OLE DB Provider for Visual FoxPro”
Type the file path of a Visual FoxPro database(e.g. C:Address.dbc).
Test the connection by clicking button “Test Connection”
Click “OK” > “OK” to apply.
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
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.
Create a new SQL Server Integration Services(SSIS) package.
Add a Data Flow Task(DFT) to the package.
Select the DFT, and go to Data Flow designer page.
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.
Double-click the OLE DB Source, click “New” > “New” to create a connection to Visual FoxPro.
In the Connection Manager, please select “Odbc Data Provider”
Select the DSN we created before, and then test the connection, make sure it is successful.
Click “OK” > “OK” to apply.
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://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx
See also this very helpful blog post by Todd McDermid
Quick Reference: SSIS in 32- and 64-bits
Also important suggestion by Scott Stauffer from the comments
If you run the package interactively in BIDS, right click the project and choose properties, choose Debugging on the left of the properties dialogue box, then change Run64BitRuntime from “True” to “False”. I would suspect this should work.
To run the package from a SQLAgent Job, go to the properties of the step of a type SQL Server Integration Services Package, and click on the Execution Options tab, then choose “Use 32 bit runtime”
This is also an important relevant thread
Bellow is the original text of the blog, which is not really relevant anymore.
———————————————————————————
In this short blog post I’d like to emphasize the importance of a decision which SQL Server version to install – 32 bit or 64 bit in 64 bit OS. If you install 64 bit version of SQL Server, be aware, that you may not be able to utilize data from many non SQL sources, that don’t have OleDB or ODBC driver in 64 bit version.
Say, you’ll get the following error attempting to invoke Import/Export wizard for VFPOleDB:
TITLE: SQL Server Import and Export Wizard
——————————
The operation could not be completed.
——————————
ADDITIONAL INFORMATION:
Feature is not available. (Microsoft OLE DB Provider for Visual FoxPro)
——————————
BUTTONS:
OK
——————————
===================================
===================================
Feature is not available. (Microsoft OLE DB Provider for Visual FoxPro)
And if you try to expand this error, you’ll get this information:
——————————
Program Location:
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Microsoft.SqlServer.Dts.DtsWizard.DTSWizard.GetOpenedConnection(WizardInputs wizardInputs, String connEntryName)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnLeavePage(LeavePageEventArgs e)
—————
None of the methods outlined in this WiKi article
Visual FoxPro Data From SQL Server will work for you.
Just something to be aware of.
Here is a reply by MSFT employee on this topic:
_There is no 64-bit VFP OleDB provider. That is why we can’t import data from VFP to a 64-bit SQL Server. The workaround in the link you provided won’t work too, as it is using the VFP provider too. This can’t work in 64bit SQL Server.
In order to work around the issue, we can firstly install a 32-bit SQL Server, import the data from VFP to this 32-bit SQL Server, and then synchronize the data from the 32-bit SQL Server to the 64-bit SQL Server.
This workaround is available too in other scenarios that there is no 64-bit provider and we are using 64-bit SQL Server.
In other words, if you need working with data in other formats, use 32 bit version of SQL Server.
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
