How to import dBase tables into SQL 2005 using SSIS
Ah, SQL Server Integration Services (SSIS). Just when I'd learned to love Data Transformation Services (DTS) in SQL 2000, they completely changed how Extraction, Transformation, and Loading (ETL) is done in SQL Server 2005.
That's okay. Something else to throw on the resume.
At my current position I have to read in dBase files (DBF) a lot. We use ESRI ArcMap and everyone here uses ESRI shape files, which put data in DBFs. So I had to learn how to read in DBFs in SSIS.
I was going to go into all the troubles I had doing this, but the fact is there is just so much that can go wrong here. Here is one way to make things go right:
- 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.
Hi Timothy,
ReplyDeleteThanks for this explanation! It works perfectly! Great job!
Terrific! Glad to help. I'm still finding out all there is to know about SSIS and its pitfalls. Hopefully I'll have more posts about it.
ReplyDeleteWow... at my company, we are using SQL 2000. Right now, we do a lot of work extracting data from SQL into dbf files (we use DTS to do all of this); complete opposite of what you do. A lot of programming is built around those dbf files.
ReplyDeleteBut soon, the main SQL database for our business order system will have to be upgraded to SQL 2005 because of a new release of that order system. I was hoping that I didn't have to touch the transformations, but it sounds like I will have to learn all of that again in some different form.
I guess it's nice to know that it will be an issue. Tomorrow, I suppose I should get a book on SQL 2005. Hopefully, SQL2005 will still support the dBase III format.
Yes, SQL 2005 SSIS is very different from SQL 2000 DTS. Some good news: 1) SSIS will support dBase III, IV, and 5.0. 2) Also, there seems to be some legacy support for DTS packages, so if you have a lot of time invested in those, I think you can bring them over. I have not brought over any DTS packages into 2005, so I can't say what the caveats are.
ReplyDeleteI'll check up on it. The Internet is somewhat sketchy on if SSIS will export directly to dBase III. The articles talk about having to export to MS Access first, then exporting to dBase III (what a pain, not to mention slowing up the process!!).
ReplyDeleteI have seen articles about bringing over 2000 DTS packages to SSIS. I'd rather learn the new SSIS, but I'd like to directly export to dBase III with SSIS, and not worry about having Access in the middle.
Hopefully, SSIS can export from SQL 2005 directly into a dBase III file.
Hi Bill,
ReplyDeleteSSIS will definitely export directly to dBase 5.0-- the main twist is that when you create the connection, you have to put "dBase 5.0" in the extended properties, then use that connection as the connection for the OLE DB Destination object. I assume this could work if you put "dBase III" in the extended properties as well. I can try it out at work tomorrow. Hope it works.
Thanks for the information.
ReplyDeleteI confirmed yesterday that the applications that use the dBase III files will not work with dBase V format (can't read them).
So hopefully, SQL 2005 will be able to export to dBase III. Otherwise, I'll be spending the next 6 months looking for a solution to support those applications.
Thank you a million times over for these steps. I don't normally work with dBase but had to move a legacy system into SQL 2005 and this was a lifesaver.
ReplyDeleteHi Timothy,
ReplyDeleteI am lucky I came across your blog. I actually use ESRI software and have been cracking my head as to how to transfer SQL Tables to DBF in SQL 2005. I followed your steps in reverse, and I get an error when trying to create the output dbf table. It sees the fields but tells me that it cannot be created due to a field
The table cannot be created.
------------------------------
ADDITIONAL INFORMATION:
Field 'MSDateStartConstruction' already exists in table 'OLE DB Destination'. (Microsoft JET Database Engine)
There is not table with such name at the root.
Also, first we wanted to connect directly to the sql server to get the table in ArcMap9.2, but apparently ArcMap does not like KEY fields.
Thanks in advance,
Luis
Luis, I think I know what the problem is. DBF format does not handle long column names well. It only recognizes the first eight characters. So if you have a table that has a field called 'MSDateStartProject' and another called 'MSDateStartConstruction', the DBF will think those are two columns with the same name ('MSDateSt').
ReplyDeleteI find that when working with DBFs it is best to keep everything (table name, column names, file names...) less than eight characters.
I hope that helps. Comment back if you need more help.
Thanks for the quick reply,
ReplyDeleteYes, I found that out too when I started shriking the field names. It created the dbf, but it crashed at the time of inserting the data. I had to use "data conversion" to change the unicode to text stream. Then this happens
[CIIS Destination [146]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
[DTS.Pipeline] Error: component "CIIS Destination" (146) failed the pre-execute phase and returned error code 0xC0202025.
It is created but it cant write to it. I checked permissions and anyone can modify within the folder and subs.
Thanks again Timothy.
Luis
Oh wow, yeah, I think I ran into this as well. There may be one last way out: create a staging table on the SQL Server side that has the right column names, column types, encodings, etc, and create an SSIS to pass data from the original SQL Server table to the "dBase Friendly" SQL Server table. Then create an SSIS to pass the data from the dBase-friendly SQL Server table to dBase. I thinki I had to do this at one point, as well.
ReplyDeleteBeyond that, I'm no longer working in a SQL Server shop, so I don't have access to an SSIS installation to try things out. The general rule on this is that dBase is very tricky! It doesn't like anything weird at all. Sorry this is so frustrating.
Thanks again Tim.
ReplyDeleteI have tested using single columns at first. I grabbed two fields from the SQL table convert them to Unicode String (DT_WSTR) and it liked that. It entered into the dbf file that had the fields as VARCHAR(50). Man, I am so glad this worked out. I will continue adding fields and run it until the whole thing runs. I love the internet. Thanks for your help Tim and I am glad you started this blog. If anybody needs more help on this, I can be reached at mi_tupac@yahoo.com not that I am a master (been doing it for 3 days) but I can sure tell them what I did. Thanks again Tim.
Luis
I was having such a had time until I came across your blog. This is great! Has anyone tried to using variables for the source file connection. I have a number of files to loop through and I can't get it to work with variables.
ReplyDeleteHi Andy,
ReplyDeleteI tried to figure out how to set up an SSIS feed using a variable for the input file name a zillion times. It was kind of my holy grail. I never did figure out how to do it. Maybe someone else who reads this can help.
As it is, I'm in an Oracle shop now and don't have access to a SQL Server instance to try this out with.
Good luck!
Hi timothy
ReplyDeletei'd love to tell you how good and easy this is to follow.. I have been trying to do this since january and i have been reading guides from all over and this is the first one that has been any use to me... i am stuck on saving all of this data to my database now..
Hi Timothy:
ReplyDeleteI have a file with a field type MEMO and I can't read from SSIS.
When i removed the field and tried it again, it worked.
What can I do?
Thanks for the post, Timothy! Getting ready to link up SQL 2005 with a dbase Spec database in the next week or so.
ReplyDeleteI was a couple of years behind you at a nearby school in Spurger, TX ('86). Got to visit Barcelona (Manresa / Balsareny) a couple of years ago as well - wonderful place! Keep up the good work.
Wow, Spurger! I hadn't heard that name for a long time. I never got to Manresa, though I talked to the guy who started Manresa Perl Mongers a couple of times when I was setting up Barcelona Perl Mongers. Keep me posted on how your migration goes, it has many putfalls but goes smoothly once everything is in place. TimothyChenAllen@gmail.com
ReplyDeleteHi Timothy. Thank you for this!!! I've been going crazy trying to figure out what was wrong with some of my dbfs, never thought about the 8.3 naming. Maybe I'll make my deadline after all. ;-)
ReplyDeleteHi Timothy,
ReplyDeleteI 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 wasnt able to get anywhere for reasons i have no idea about. Anyway on surfing the net i saw your comments abt using BIDS to import data and i tried it out.
I did see some progress after following all ure steps such as creating a connection to the folder containing all the DBF files as well as create a data flow task etc. The progress that i got is that i was able to view all the tables individually.
But now my doubt is actually when you ended the post about how to import data you metioned that you can the now use the DBF connection as a data source.
Well i am not sure if what i did was the right thing to do but anyway here is what i did i went back to SQL Server 2005 clicked on import and from the list of options for data source i wasnt able to find any changes. I expected that i would get an additional optional that would save me from all my troubles.
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.
Thanks!!
ReplyDeleteYou can help me a lot
after trying to find dbase III provider all morning T_T
Can you give more details please? I do not know anything about SSIS...so I do not even know how to do step 2. THanks!
ReplyDeleteI'm sorry I can't give more detail. I no longer work on SQL Server. If you're very stuck, email me and I'll try to walk you through it: TimothyChenAllen@gmail.com.
ReplyDeleteNo promises, though; it really has been a year since I've done this.
Thanks for your explaination!
ReplyDeleteEighteen April
I had some troubles with swedish characters not reading correctly from the dbase 5.0 driver. This was fixed by changing the registry setting [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Xbase\DataCodePage] = "ANSI" instead of OEM. OEM caused a conversion that lost my special characters.
ReplyDeleteHope this can help someone.
Thanks, Richard!
ReplyDeleteTCA, your original post was fantastic, thanks for that help.
ReplyDeleteQuestion: how can I use the same information for creating a Linked Server to my dbase database, using either the GUI in MSSMS, or the sp_addlinkedserver command?
Thanks,
Steven
Steven, sorry, I've been out on vacation for a while. I'm not sure how you can make this a linked server. I don't have access to a SQL server anymore. If you get really stuck, email me at TimothyChenAllen@gmail.com and I'll do my best. But I don't have consistent email access while I'm on vacation in Spain, so it might take a while. Good luck.
ReplyDeleteTimothy
ReplyDeleteYou really really deserve a great praise from me.
I was working on this solution in vain and you have the perfect perfect answer.
Eric
Glad to help, Eric!
ReplyDeleteHi Timothy,
ReplyDeleteI can't import DBF to SLQ2005 by SSIS :(
All options is correct, but occurs error "Index file not found". The name of file is in 8.3 format.
What's wrong?
admin, the only other thing I would think of that could go wrong here would be that you may have column names that are longer than 8 characters. Other than that, review that when you specify the database name, don't put the complete path all the way to the .DBF file; put only the path to the folder containing the file.
ReplyDeleteSorry I can't help more, I'm not in a place where I can test any theories as I no longer work on SQL Server regularly. Good luck!
You right! One field's name has more than 8 symbols really!
ReplyDeleteI've fixed this problem by installing OLE DB Fox Pro 9.0 drivers and using it.
Thanks a lot for reply!
Hello Timothy,
ReplyDeleteI am trying to read a huge DBF Files having 12 million records. And when i try to do that, SSIS package gives error "Could not update, source locked by user (unkown) at machine ."
1. I am using DBF file as source and SQL table as destination.
2. I am also using a parameterized query at source to get few records based on input dates.
Every thing works if there are <100 K records.
Any idea?
Thanks !
devoutingdev,
ReplyDeleteI'm unaware of a size limitation on this, but it would not surprise me. One thing: check to see if there are individual records that do not match the data type you are importing in the dBase file. In other words, I would try cutting the size of the file until it started to work. Then try pinpointing the record it stops working on. It might be that SSIS has decided that a record is an integer based on the first 100 records or so, but a later record has a value that requires a long.
I hope that helps.
Hi Timothy,
ReplyDeleteIt is a great post!
I have done all the steps as you mentioned.But when I hit Preview I get the following error
=============================
Error at DataFlow Task: An OLEDB error occured.
Opening a rowset fro "BREAKOUT" failed.Check that the object exists in database
=====================================
Please help..
Remya,
ReplyDeleteThis probably has to do with an object in the database with a bad name. dBase databases need table names, column names, and file names to all be eight characters long or less.
The other thing that can go wrong is if the name of the database file has a space in it, or the path has a space in it. For example, "C:\program files\foo\bar.dbf" can cause trouble. Move the database to a simple directory name; e.g. "c:\db\bar.dbf".
If that doesn't work, email me at TimothyChenAllen@gmail.com and we'll work it out. Thanks.
I hate hate hate hate hate websites that play music / sounds, especially if it's not very clear. Please remove it!
ReplyDeleteOtherwise cool info here
BUT GET RID OF THAT CRAPPY MUSIC!
Anonymous, I'm a singer/songwriter. I'm sorry you don't like my music. I'm glad you are able to use my code.
ReplyDeleteI won't be removing my music-- I love writing, singing, and recording; my web page is the one place I get to indulge that.
One thing: please don't call it crap, at least. I work hard on my music with very limited time and resources to dedicate to it. It isn't crap to me; I'm proud of what I've been able to do. And it seems churlish to me that you make use of my code and insult me.
Hi Timothy, what do you think about the dbf file recovery program, it automates the procedure of database recovery.
ReplyDeleteyour music sounds cool :)
YOU ROCK TIMOTY!
ReplyDeleteThis solution would work with a single file. What about multiple dbf files in same folder and read all of them?
ReplyDeleteTimothy just wanted to thank you for spending the time almost 10 years ago to lay this out. There are still a lot of these older technologies in production and you saved me considerable time here. Cheers to you!
ReplyDeleteHi Allen,
ReplyDeleteI have done all the steps you have mentioned to import DBF file in SSIS. But I am getting error for some DBF files.
Opening a rowset for "XXXXXXX" failed. Check that the object exists in the database.
Please can you help me on this.
With regards,
Ananta
Hi Ananta, I haven’t worked in SSIS in a while. I did find a thread about that error: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6156ec5d-ef2c-45b8-9260-397e4680cf25/cant-pass-dynamic-ssis-connection-string-to-sql-server-agent?forum=sqlintegrationservices
DeleteGood luck with it.
Hi Anata, I found I had answered this problem before:
DeleteThis probably has to do with an object in the database with a bad name. dBase databases need table names, column names, and file names to all be eight characters long or less.
The other thing that can go wrong is if the name of the database file has a space in it, or the path has a space in it. For example, "C:\program files\foo\bar.dbf" can cause trouble. Move the database to a simple directory name; e.g. "c:\db\bar.dbf".