DTS sql 2005

Question

Post reply

DTS sql 2005

Khaled Mahmoud Mansour

Newbie

Points: 1
More actions
December 12, 2006 at 5:45 am

#114876

How to import DBIII files into sql 2005 using the import facility?

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

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

Kirit Newbie Points: 7 More actions · Answer 1

After tons of searching I found this...

http://msdn2.microsoft.com/en-us/library/aa337084.aspx

How to: Connect to a dBASE or Other DBF File

You can connect to a dBASE or other .DBF database file in an Integration Services package by using an OLE DB connection manager and selecting the Microsoft OLE DB Provider for Jet 4.0.

Note:
The SQL Server Import and Export Wizard in SQL Server 2005 does not support importing from, or exporting to, dBASE or other DBF files. You can use Microsoft Access or Microsoft Excel to import the data from DBF files into an Access database or Excel spreadsheets, and then use the SQL Server Import and Export Wizard.

I just love MS.

Kirit Newbie Points: 7 More actions · Answer 2

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