How To Import DBF Files Into SQL Server 2005 Tabbles

  • Jay Sapani

    Mr or Mrs. 500

    Points: 543

    I Want To Import Dbase Files In To Dbf Tables And I Tried SSIS Of SQL SERVER 2005 But Was Not Able To Do It. Anybody On How To Do IT.

    Also Any One Can Suggest Good Book For Learning SQL SERVER 2005 DTS Or SSIS

  • Peter Schott

    SSCrazy Eights

    Points: 9485

    Good book – look at Professional SSIS from Wrox or the new release from MS Press for which the name escapes me at the moment.

    As for Importing DBase files – you’ll need to configure your connection manually to connect. Set up a Jet OLEDB Connection – point to your folder containing the DBase files. Click the “All” button and change the “Extended Properties” to “DBASE IV”. For Paradox files, you’ll use “Paradox 5”. For Foxpro files, you’ll need to use the OLEDB Provider for Foxpro and point it to your folder/file. I didn’t have any major issues with this that I remember.

    Basic steps:

    Create a connection to your DBase files.

    Create a connection to your FoxPro files

    Create a Data Flow task

    Add an OLEDB Source – assign this to your DBase connection.

    Add an OLEDB Destination

    Add any transforms needed between the Source and Destination or just drag the arrow between them if no transforms are needed.

    Once you’ve done this, double-click the destination to set the properties.

    Assign the FoxPro OLEDB connection to the destination, check the mappings.

    That should handle everything – click the “Run” button and you should be started. You may need to tweak things to convert successfully, but this would be the basic method.

    -Pete Schott

  • Jay Sapani

    Mr or Mrs. 500

    Points: 543

    Thank you Passchott For Your Sugesstion And Reply.

    I Request You If U Can Make the Package As per U R Reply And Email Me The Same at jay_sapani@yahoo.com As i am a new to it.

    Thanks In Advance For Package

  • Peter Schott

    SSCrazy Eights

    Points: 9485

    Only problem is that I don’t have any DBase or FoxPro files that I could send to you. I use those from other customers and can’t create them. As they are customer files, they contain sensitive information and can’t be sent on. The shell wouldn’t help too much there as you’d need to modify all sorts of things to get it to work.

    I’d recommend giving it a try and posting back if you have any problems. It’s not too hard, just not incredibly straightforward.

    -Pete

  • Jay Sapani

    Mr or Mrs. 500

    Points: 543

    I Dont Want DBF Files. I Just Want Package Otherwise if u want i can send my dbf at your email id.

    post u r email id or mail to me at my id and i will send u my dbf

  • Peter Schott

    SSCrazy Eights

    Points: 9485

    Jay, please try to work through the steps. If you encounter a problem, post details. It’s not so difficult that a little trial and error should see you through. Perhaps try working with DBF to text first, then DBase to text, then try working between the two of them. I really think you’ll be better off in the long run trying it out, then posting if/when you have trouble.

    -Pete

  • Iain R

    SSC Enthusiast

    Points: 169

    Pete,

    I’m having a similar problem with Paradox files –

    I’ve followed your advice in your original post up to this point

    My Jet OLE connection is the path of the .db Paradox table, and I have edited ‘Extended porperties’ to read “Paradox 5”.

    When I try to assign this connection to my ‘OLE DB source’ in Control Flow, I can choose the connection OK, but when I attempt to select “Name of the table or the view” I get the following error:

    ‘Could not find installable ISAM’

    If you have any ideas – I would be very grateful

    all the best,

    iain

  • Peter Schott

    SSCrazy Eights

    Points: 9485

    Best guess is that you need to have the Paradox drivers installed. I have Paradox 9 installed on my machine so I don’t expect any major issues. I’ll do some checking on my config later today to see if I have anything unusual in my Paradox setup.

    -Pete

  • Iain R

    SSC Enthusiast

    Points: 169

    Thanks Pete,

    Did you have any luck?

    I’ve managed to use my old SQL Server 2000 steps and got my most crucial package to work, but I’d rather not do that!

    all the best,

    iain

  • Peter Schott

    SSCrazy Eights

    Points: 9485

    I actually have “Paradox 5.x” in my Extended Properties. Perhaps that’s worth a try?

  • Iain R

    SSC Enthusiast

    Points: 169

    It works!

    I can’t believe I hadn’t tried that. My alternative solution was to hunt for an ODBC connection manager route – but that was equally frustrating.

    Now sorted.

    Many thanks Pete!

    all the best,

    iain

  • pablo cecere

    SSC Enthusiast

    Points: 116

    Pete, I am kind of lost …

    Where do I create the connection and all this you are talking about?

    In the Management Studio?

    Thanks

    Pablo

  • Peter Schott

    SSCrazy Eights

    Points: 9485

    We were talking Integration Services – you’ll need to use the Business Intelligence Design Studio (BIDS) to create a new SSIS Project & Package. You define all of these settings from within the SSIS package. SSIS is definitely a change from DTS. If you’re not familiar with it, you may want to watch a couple of webcasts to see it in action and get an idea of how people are working with it.

    -Pete

  • Edward C Smith

    SSC Enthusiast

    Points: 153

    Hi Peter,

    I read your post as I’m trying to do something similar. I have a large repository of what appear to be paradox table files from our primary customer application. My hope is to convert these into SQL Server for Warehousing and reporting purposes.

    I have setup my Paradox 5.x OLE DB connection and I can see the tables but I get an error when I try to map columns “SSIS Error Code DTS_E_OLEDBERROR.” The next message is “Opening a rowset for “USER” failed. Check that the object exists in the database.” where USER is the table I’m attempting to access.

    Admittedly I know very little about paradox, the only information I Have about these files is from a google post saying they were Paradox .db files.

    Thanks for any help any one can offer.

    -Ed

  • pablo cecere

    SSC Enthusiast

    Points: 116

    i use Full Convert to pass DBF to SQL Server.

    I got a license.

    Send me your DBF, I send you the script.

    Pablo

Viewing 15 posts - 1 through 15 (of 37 total)

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