Import data From DBf file to SQl 2008

  • Hi,

    I want to import data from .dbf file to sql 2008.

    I'm using the Import Export wizard but there is no data source for dbf File.

    So no clue how to import it.

    Thanks

  • Use an ODBC conneciton to the .DBF file, if you do not have the driver for this then it shouldn't be an issue to download it..

  • Hi,

    I'm trying to find the ODBC Driver but cannot find.

    Sql Server is installed in Windowe Server 2003 Enterprise X64 Edition

  • There should be two ODBC drivers in Win2003 x64 but you need the x86 which is 32bits because DBF are all x86 which is 32bits. Check the WOW64 directory if you cannot find it post again. You should also know you must build your code in the WOW64 directory so the system knows you are developing in 32bits.

    Kind regards,
    Gift Peddie

  • I did this yesterday.

    To configure a connection manager to connect to a dBASE or other DBF file

    Add a new OLE DB connection manager to the package. For more information, see How to: Add a Connection Manager to a Package.

    On the Connection page of the Connection Manager dialog box, select Native OLE DB\Microsoft Jet 4.0 OLE DB Provider as the Provider.

    Therefore the Database file name text box must contain the path of the folder where the DBF file resides, and must not include the file name itself. You can type or paste in a folder path, or you can use the Browse button to select your DBF file, and then remove the file name from the end of the folder path.

    On the All page of the Connection Manager dialog box, enter dBASE III, dBASE IV, or dBASE 5.0, as appropriate, as the value of Extended Properties.

    Click Test Connection to validate the values that you have entered. You should see the message, "Test connection succeeded." Click OK to close the message box.

    Click OK to save the configuration for the connection manager.

    Kind Regards, Will

  • Ok all the final word on Jet

    The Microsoft OLE DB Provider for Jet and the Microsoft Access ODBC driver are available in 32-bit versions only

    http://support.microsoft.com/kb/957570

    So frankly what this is telling me is if your using the 64 bit version of SQL server than you can not connect to excel files either via a linked server, OPENDATASOURCE, or reporting services. WOW.

    Talk about a chink in the armor I'm stunned. There are articles on the web that show how to run SSIS in 32 bit mode to support Jet or a IIS server and other MS apps in 32 bit mode, but is that really a solution or a step backwards. :crazy:

    So lesson learned is step back and think about that for a second before you decide to upgrade your server from 32bit to 64bit. You can go out on the web and read the horror stories of people who did, only to break existing code (ouch).

    Peace out...

  • Hi,

    I have a problem with the 64 bit.

    I can run the package manually by double clicking the package and execute but if i schedule the package from the sql job agent, then it fails and gives this error:

    Message

    Executed as user: ACCOUNTS\sumit.saini. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:50:28 AM Error: 2009-02-11 09:50:29.65 Code: 0xC0209303 Source: PMI-UPDATE Connection manager "Connection 1" Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider MICROSOFT.JET.OLEDB.4.0 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2009-02-11 09:50:29.65 Code: 0xC020801C Source: COPY AVAILBLE FROM J DRIVE OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Connection 1" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2009-02-11 09:50:29.65 Code: 0xC0047017 Source: COPY AVAILBLE FROM J DRIVE SSIS.Pipeline Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. End Error Error: 2009-02-11 09:50:29.65 Code: 0xC004700C Source: COPY AVAILBLE FROM J DRIVE SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2009-02-11 09:50:29.65 Code: 0xC0024107 Source: COPY AVAILBLE FROM J DRIVE Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:50:28 AM Finished: 9:50:29 AM Elapsed: 1.047 seconds. The package execution failed. The step failed.

  • You cannot run it with the 64bits Agent look for the 32bits Agent in the WOW64 directory amd run it from there because when something is 32bits all must be 32bits in most cases.

    http://support.microsoft.com/kb/918760

    To make the package work when started from a SQL Server Agent Job, you need to execute the package using the 32-bit version of DTExec, also check below to make sure your package property is showing Run64bitsRuntime False.

    http://blog.n-technologies.be/post/2008/08/26/Import-Excel-files-with-SQL-Server-Integration-Services-on-a-64-bit-server.aspx

    Kind regards,
    Gift Peddie

  • Microsoft may have an easier way in SS 2008. To run a package by using a SQL Server Agent job:

    ?To run a package in 32-bit mode from a 64-bit version of SQL Server Agent, in the New Job Step dialog box, on the Execution options tab, select Use 32 bit runtime.

    This was found at How to: Run a Package

    http://msdn.microsoft.com/en-us/library/ms138023.aspx

    I tried it for exporting to MS-Access and it worked for me.

    Steve

  • Actually, it is not a step backward.

    You have 64bit entity (server) and you want to read something from 32bit entity (Dbf file). To make it work both entities should be on the same platform.

    It would be nice if you can make a Dbf file 64bit compliant (please let me know if you can, we could be rich then B-))). That leave you one option only and it is to downgrade your 64bit entity to 32bit and then they can talk each other and live happily ever after (or until you load all data and wipe off your Dbf data source).

    Cheers, Marin

    Marin Kostadinovic
    ______________________________
    Database Administrator/Architect
    dimm-is.com
    freemerlin.com

  • Success. The 32 bit option was a very important thing to learn about in dealing with the Visual Fox Pro OLE DB driver.

    Thanks.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply