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.