November 16, 2009 at 10:13 am
Hello all:
Here is the situation:
I was just assigned to this new project...12 centers will FTP their data in a Zipped Folder that has .DBF and .FPT files in it...They then want all the data consolidated into one database...However when I went to create the db, I needed to see how the file was structured so I would know how to set up my table and when I tried opening one of the .DBF files, the window popped up saying it didnt know what to open it with - so I tried Notepad, Wordpad, MS Word, MS Access, and SQL Server...It would open up but it was in such a format that I couldnt tell what it was so I dont know how to proceed...Can anyone help me? I tried googling and some the things I read said it could be opened in MS Access but when I tried to do it said that the External Table was not in format...I need to know how those files are set up so I can be able to get started with this project! Any help is appreciated...if I have not given sufficient info, just ask and I will go into more detail!
Thanks
November 16, 2009 at 10:42 am
.dbf files are from dBase
http://en.wikipedia.org/wiki/DBase"> http://en.wikipedia.org/wiki/DBase
you should be able to create an ODBC connection to this file to import the data either using SSIS or any other ODBC capable tool.
November 16, 2009 at 11:07 am
Thanks for your response, however when trying to create an SSIS package, I tried creating a new connection but I dont see New OBDC connection...I have a New OLE DB connection but that just asks for server name and database name on the server...Well I used that to connect to my database on my local machine but that is not where that .dbf is...They come in a compressed zipped folder and once you open the folder there are mulitple .dbf and .fpt files in there...How do I connect to them? I cant use flat file connection b/c its not a flat file...Im lost!
November 17, 2009 at 9:10 am
You should be able to use the FoxPro OLE DB Driver to access the data. It will need to be downloaded and installed on the SSIS development machine and the SSIS SQL Server that the package will be deployed to. Here is the link to the download page.
I am currently using this driver within SSIS to transfer data from .dbf files (FoxPro free tables) to SQL Server. The .FPT file is where FoxPro stores MEMO (think blob) data so just connecting to the .dbf's those files are associated with should allow you to get that data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 19, 2009 at 10:02 am
My company loads DBF files. In SSIS, you can set up a Data Source for this by using the following settings:
Choose "Native OLE DB\Microsoft Jet 4.0 OLE DB Provider for the Provider.
Place the DBF files into a folder - indicate that the Database file name is the UNC file path for the folder! (i.e. if the files are in \\myserver\DBF Folder, that should be the entry!)
Under Properties for the connection - Extended Properties - enter in the following: dBase IV
Good luck!
Todd
November 19, 2009 at 10:14 am
Can you please give me a step by step directions to setting up this in a SSIS package...Im new to SSIS so Im not quite sure what I am doing! I have set up a connection to my local machine with the database that I want the files to go in to but I dont have a table yet in the db b/c I cant set the table up b/c I cant see how the data is made up...Therefore it is giving me an error when trying to establish that connection! And I see where I can establish a OLE DB Source but I dont see where you can choose the Microsoft Net Provider...And the .dbf and .fpt files are in a zipped folder already! But what kind of source is that? Sorry for my incompetance but Im new to SSIS and have only worked with it a couple of times!
Also everything I have researched says I should be able to open up .dbf and .fpt files in Access but everytime I try, I get an error!
IF ANYBODY HAS ANY SUGGESTIONS, PLEASE HELP!!!!
THANK YOU SO MUCH!
November 19, 2009 at 10:23 am
I can attempt to give you a step by step - here goes.
1) Open up Business Intelligence Development Studio
2) Create a new project selecting Integration Services as the package type.
3) Go to ControlFlow tab. Drag a "Data Flow Task" onto the ControlFlow.
4) Double-click on the Data Flow Task.
5) Drag a "OLE DB Source" onto the Data Flow tab. Click New... and New... again.
6) Now you see the "Connection Manager" - follow my directions from before.
7) On the OLE DB Source Editor, you should be able to select "Table or View" for the Access Mode, and the "Name of theTable or the View" will list all of your DBF Files (if you have done this right). You'll have to create one of these Data Flows for EACH line.
More details on how to use SSIS are in the SSIS Tutorial. Check out SQL Books Online for this. Hopefully this will help!
Todd
November 19, 2009 at 10:24 am
One additional note: You DO need to unzip the files into a new folder. SSIS will not read zipped files directly, nor would I recommend that approach anyway. This could be why you are having troubles...
Todd
November 19, 2009 at 10:47 am
OK thanks...I have done steps 1-7 and got the .dbf and .fpt files in a separate folder...And when I set up the connection, i chose table or view and then i clicked the drop down box and there were all my files...HOWEVER when I clicked ok or if I clicked the columns tab on the left...I get the following error:
Error at Data Flow Task [OLE DB Source[1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error Code: 0x80004005.
Error at Data Flow Task [OLE DB Source[1]]: Opening a rowset for ".dbf file" failed. Check that the object exists in the database.
Of course it is not in the database...I dont have a table in the db. I am wanting to put all these .dbf files in a table but I cant set up a table if I dont know how the data is structured. What do I need to do?
Thanks
November 19, 2009 at 10:56 am
Hmm - your Columns part should work. That may be an issue...
So, now you drag a "OLE DB Destination" to your Data Flow, drag the green arrow from the Source to the Destination to connect them, and then you should be able to double-click on the Destination. At that point, it will allow you to create a new table BASED ON the structure of the DBF file!
You may have to Google for the error that you are currently getting on the DBF file. It is possible that maybe my DBASE IV does not apply for all DBF files, but maybe only version 4?
Todd
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply