At work, we are building some content management utilities to keep track of our training materials and document all of our content in a database. To get a head start, our marketing team started a large Excel spreadsheet to list, categorize, label, and tag our hundreds and hundreds of resources. We decided to attempt to automatically import some of the content directly from Excel to keep them from having to hand-enter it again. Seeing as how we are wandering around in the cold, dark, stone ages of SQL Server 2000 I thought I would throw the .XLS file at a DTS package and see what happened.I'll admit, I haven't had to import many Excel spreadsheets into a database before, but I didn't want to waste much time on it and DTS packages (Another thing I've never really had to mess with much) seemed like a pretty handy and reproducible method of doing the import. My goal was just to dump the 2000-some records straight into a SQL Server table; and at first, it looked like it might be ridiculously easy. I created a new DTS package and slapped on a "Microsoft Excel 97-2000" connection which I pointed to our Excel sheet of doom. Next I added a "Microsoft OLE DB Provider for SQL Server" connection object and pointed it towards my dev server. Finally, I added a "Transform Data Task" between the two. When I went to go choose the destinations, I hit the "create" button to automatically create a table for me that matched the spreadsheet. I just wanted to use this table temporarily. I thought it was interesting that the DTS chose an nvarchar255 for every column in the spreadsheet. My first annoyance was that the marketing team didn't use the first row of the sheet to put their column headers in. As a result, the table which was created for me had column F1, F2, F3, through F34. I had to go through and manually type in the column names anyway so they would be usable. While I was at it, I changed the nvarchars back to varchars since I was pretty certain there was no Unicode goodness in the spreadsheet. Everything looked good, so I crossed my fingers and clicked "Execute". Boom. It errored right away. Of course, the interface didn't seem to want to give me any hints as to WHY it had errored. I dug through the options tab on my transformation task and found an "Exception File" setting which I pointed to a text file on my desktop. I ran it again, and the exception file had reported the following error:

Error Source: Microsoft Data Transformation Services (DTS) Data Pump

Error Description:Data for source column 9 ('F9') is too large for the specified buffer size.

Ok, no problem. The 9th column was "long Description. Remembering that everything had been created as 255 character fields, I bumped that column up to a text. No luck. I then went back through all the columns in the spreadsheet and found that marketing had "helpfully" hidden the columns they didn't want to see, so all my column names were off by two or three. Fixing them and setting the NEW Long Desc column to text was to no avail. Exasperated, set ALL the columns in my table to text but I was still greeted with the same smiling error message laughing at my defeat. After a round of Googling the sparse interwebs on the topic, I tried changing my SQL Server datasource over from "Microsoft OLE DB Provider for SQL Server" to "Microsoft ODBC Driver for SQL Server" Victory was mine for a few fleeting seconds while the progress bar began to advance. Then my hopes were dashed again with another error. This one quite a bit less useful than the first:

Error Source: Microsoft OLE DB Provider for ODBC Drivers

Description:Query cannot be updated because it contains no searchable columns to use as a key.

What!? I'm not even trying to update a query for goodness sake. I just want one measly insert! Apparently this was too much to ask for, and Google's results weren't being too helpful. Since both of my SQL Server connections provided me with errors, I decided to try my luck again with the "Microsoft OLE DB Provider for SQL Server" since at the least the first error made more sense. After scouring Google for a while longer I found this page:
http://support.microsoft.com/kb/281517
If the source has fields with more than 255 characters, the destination data for these fields may be truncated to 255 characters. DTS fails on the first row that has a field with more than 255 characters. To verify this, look up the row number that displays in the error message. "
This is what they claimed the cause was:
The Jet OLE DB provider reads a registry key to determine how many rows are to be read to guess the type of the source column. The registry setting is: HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows By default, the value for this key is 8. Hence, the provider scans the first 8 rows of the source data to determine the data types for the columns. ... if there is no data with a length greater than 255 characters in the first 8 rows of the source, Jet cannot accurately determine the nature of the data type.
I reluctantly opened up regedit and modified the registry key above to a value of "0". This was not recommended for performance reasons, but it was the only way to scan more than 16 rows into the spreadsheet. 16,384 rows to be exact. Success at last! No, wait-- never mind. Not only was my package still erroring, but now I was right back around to the error I received from the ODBC version of the driver: "Description:Query cannot be updated because it contains no searchable columns to use as a key." It seemed fate wanted me to battle it out with both errors before I could enjoy the spoils of an imported spreadsheet. Back to Google I went, tearing through antiquated message boards, forums with useless RTFM "help", and those really annoying SQL Server sites that show up in Google's results but give you a "Join Now" screen when you click on them. I couldn't figure out why it complaining about needing a primary key for updating when all I was asking for was an insert into an empty table. Running low on patience and sleep (2 AM in the morning) and bowing to the voice of desperation, I threw a uniqueidentifier column on my temporary SQL Server table with a default of (newid()) and set it as the primary key. But wait! What's this I see before me now? The progress bar was advancing uninterrupted! I waited breathless, fearing that early celebration itself might give cause for more evil to appear in the error logs. Finally, after several unproportionally long minutes of waiting, Microsoft's DTS waved a while flag of surrender, and succumbing to my will, successfully imported 1,749 glorious records from the clutches of Excel into the hands of SQL Server.