August 8, 2008 at 8:47 am
HI,
I am a newbie when it comes to SQL Server 2005. The problem that i am facing is that i am trying to import .DBF files using SQL Server 2005 but then it seemed that i wasn't able to get anywhere for reasons i have no idea about. Anyway on surfing the net i saw a forum that talked abt importing data as well i incorporated points that i found from this site abt using BIDS to import data.
I did see some progress after creating a connection to the folder containing all the DBF files as well as create a data flow task which consists of OLE DB source and OLE DB source Editor etc. The progress that i got is that i was able to view all the tables individually.
Well i am not sure if what i did was the right thing to do but anyway here is what i did after i was able to preview all the data i went back to SQL Server 2005 clicked on import and from the list of options for data source i wasn't able to find any changes. I expected that i would get an additional optional.
So right now my doubt is that i dont think i have imported these tables into SQL Server 2005. I see the the tables if i click on preview but i dont know the next step to import all these files to SQL Server 2005.
I would appreciate any help from you. thank you.
August 8, 2008 at 9:22 am
A DBF file is essentially a table. I assume these are from DBase/FoxPro.
You import each one separately. You could write a loop, but how many tables are there and is this a one time thing or something you'll do every xx days?
August 8, 2008 at 9:44 am
There is a lot of tables. Its like there are 4 folders within each folder there are 12 different categories and each of these categories consists of atleast 30.DBf tables. So how many days well if i can understand one category everything will be smooth. It shouldnt take me long.
you mentioned about using a loop well i have been trying to find a window where i can actually type in code like for example in SQL Server just click on New Query and you can actually type in a query or anything for that matter. In BIDS i am totally lost.
Moreover just to give u the steps that i followed. I actually thought this would import the files but now is it necessary for me to write a loop. From the procedure below i was not able to follow the last line which i seperated from the rest.
Change the name of the DBF file you want to read in to be 8.3 format. That is, 8 characters before the dot and 3 after. e.g. If the data file is named "2007 Data Load for Finance.DBF", change it to FIN2007.DBF or some such. SSIS will reckognize the DBF if the file name is not in 8.3 format, but will not be able to read data from it. This caused me much heartache before I figured it out.In SSIS, set up an OLE DB Source. Hit the "New..." button for the OLE DB connection manager". Choose "Native OLD DB\Microsoft Jet 4.0 OLE DB Provider" as the provider.For the Database File Name, put in the path to the DBF, but not the DBF file name itself. You will not be able to browse for this, because SSIS is looking for an MDB file at this point, which is not what you want. e.g. if your DBF is in c:\databases\FIN2007.DBF, put in "C:\databases\".Click the "All" button, scroll up to Extended Properties, and put in "dbase 5.0". If you don't do this, SSIS will try to read your dBase file as an Access file, which will fail. While you're here, you can hit "Test Connection" and it should work.Hit OK until you are back at the OLE DB Source Editor screen. Choose the name of the database file from "Name of the table or the view".At this point you should be able to hit "Preview..." and see your data.
You can now use this DBF connection as a data source.
August 8, 2008 at 9:50 am
The files are all Dbase files too.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy