I have been using SSIS packages to do this. Create a dataflow and use OLE DB
connection managers both for the foxpro dir and for the sql server
destination. The ole db connections are much more forgiving about the
datatypes. I have also had horrid problems with date fields in FoxPro and
using direct table access so here is my current routine:
1. Delete all (indexes) but the primary key of the vfp table and then pack
dbf the table
2. Reset all empty dates to {} and set each date to CTOD(DTOC (or TTOC)). I
hate it but it works.
3. Run my ssis package and slurp it into sql server.
If you are going to get the data by specifying a query you do not need to
pack the database. If you choose table access it will bring in the deleted
records even though the default is SET DELETED ON.
[quoted text, click to view] "Xlay" wrote:
> I have a free table directory with FoxPro tables. I need to transfer
> the data from them into SQL 2005. Back with SQL 2000, I did this using
> the DTS Wizard.
> When I try to do the same in 2005,
> 1) I use MS OLE DB Provider for Visual FoxPro. When I test the
> connection, it's successful
> 2) Select the Copy data from one or more tables and hit Next.
> At this point I get the following error:
>
> ===================================
>
> The parameter is incorrect. (SQL Server Import and Export Wizard)
>
> ===================================
>
> The parameter is incorrect. (Microsoft OLE DB Provider for Visual
> FoxPro)
>
> ------------------------------
> Program Location:
>
> at System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid
> schema, Object[] restrictions)
> at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid
> schema, Object[] restrictions)
> at
> Microsoft.SqlServer.Dts.DtsWizard.OLEDBHelpers.GetDataTable(IDbConnection
> openedConnection, String tableType)
> at
> Microsoft.SqlServer.Dts.DtsWizard.OLEDBHelpers.LoadAllTables(IDbConnection
> myConnection, UpdateProgress updateProgressCallback)
> at
> Microsoft.SqlServer.Dts.DtsWizard.StepTablesStorage.InitializeForDataSource()
>
> Has anyone met this before? Any ideas or suggestions would be welcome.
> Thanks in advance
>
