Thursday, July 5, 2007

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:

  1. 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.
  2. 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.
  3. 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\".
  4. 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.
  5. 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".
  6. 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.

44 comments:

  1. Hi Timothy,

    Thanks for this explanation! It works perfectly! Great job!

    ReplyDelete
  2. 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.

    ReplyDelete
  3. Wow... 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.

    But 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.

    ReplyDelete
  4. 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.

    ReplyDelete
  5. I'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!!).

    I 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.

    ReplyDelete
  6. Hi Bill,
    SSIS 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.

    ReplyDelete
  7. Thanks for the information.

    I 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.

    ReplyDelete
  8. 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.

    ReplyDelete
  9. Hi Timothy,
    I 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

    ReplyDelete
  10. 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').

    I 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.

    ReplyDelete
  11. Thanks for the quick reply,
    Yes, 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

    ReplyDelete
  12. 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.

    Beyond 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.

    ReplyDelete
  13. Thanks again Tim.
    I 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

    ReplyDelete
  14. 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.

    ReplyDelete
  15. Hi Andy,
    I 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!

    ReplyDelete
  16. Hi timothy

    i'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..

    ReplyDelete
  17. Hi Timothy:
    I 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?

    ReplyDelete
  18. Thanks for the post, Timothy! Getting ready to link up SQL 2005 with a dbase Spec database in the next week or so.
    I 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.

    ReplyDelete
  19. 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

    ReplyDelete
  20. Hi 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. ;-)

    ReplyDelete
  21. Hi Timothy,

    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 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.

    ReplyDelete
  22. Thanks!!
    You can help me a lot

    after trying to find dbase III provider all morning T_T

    ReplyDelete
  23. 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!

    ReplyDelete
  24. I'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.

    No promises, though; it really has been a year since I've done this.

    ReplyDelete
  25. Thanks for your explaination!

    Eighteen April

    ReplyDelete
  26. 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.

    Hope this can help someone.

    ReplyDelete
  27. TCA, your original post was fantastic, thanks for that help.

    Question: 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

    ReplyDelete
  28. 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.

    ReplyDelete
  29. Timothy

    You really really deserve a great praise from me.
    I was working on this solution in vain and you have the perfect perfect answer.

    Eric

    ReplyDelete
  30. Hi Timothy,
    I 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?

    ReplyDelete
  31. 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.

    Sorry 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!

    ReplyDelete
  32. You right! One field's name has more than 8 symbols really!
    I've fixed this problem by installing OLE DB Fox Pro 9.0 drivers and using it.
    Thanks a lot for reply!

    ReplyDelete
  33. Hello Timothy,

    I 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 !

    ReplyDelete
  34. devoutingdev,
    I'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.

    ReplyDelete
  35. Hi Timothy,
    It 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..

    ReplyDelete
  36. Remya,
    This 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.

    ReplyDelete
  37. I hate hate hate hate hate websites that play music / sounds, especially if it's not very clear. Please remove it!

    Otherwise cool info here

    BUT GET RID OF THAT CRAPPY MUSIC!

    ReplyDelete
  38. 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.

    I 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.

    ReplyDelete
  39. Hi Timothy, what do you think about the dbf file recovery program, it automates the procedure of database recovery.
    your music sounds cool :)

    ReplyDelete
  40. This solution would work with a single file. What about multiple dbf files in same folder and read all of them?

    ReplyDelete
  41. Timothy 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!

    ReplyDelete

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.