SQL2005 SSIS - won't allow text delimiters

Last Post 25 Jun 2009 06:45 AM by thisisfutile. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
thisisfutile
New Member
New Member

--
25 Jun 2009 06:45 AM
I know it's been 3 years since this post but this site was the first google hit when I searched the words: "SQL SERVER 2005 TEXT QUALIFIER" (no quotes). I thought I'd add my experience so that those that find this thread might have some direction instead of another dead-end search or some CRAZY solution that probably won't work for your situation anyway.

What I discovered:

After we upgraded to SQL Server 2005 at my place of employment, I spent two, 8-hour days on this (I felt so guilty, I apologized to my boss), and I still could not find a clean method to take a simple "flat file" and import it using the Management Studio "Import Data" wizard. If the flat file doesn't have any text qualifiers, then it works fine, but good luck finding a file that doesn't have them. In my opinion, all import processes should have a text qualifier option for the obvious reason of allowing the delimiter to be data. This is data management 101, but for whatever reason, MS decided to not support it (yet they give the option in the Import Wizard to chose a text qualifier...go figure.) I thought it was a bug, but as I write this it's June 2009 they still don't support it in the latest Service Pack and from what I understand, neither does SQL 2008. Anyway...

Solution:

While many DBA's out there probably have very slick and impressive ways to deal with this, the solution (easiest) I found without having to manipulate your flat file or get overly fancy with your import process is to choose "Microsoft Excel" as the datasource during the Import Wizard instead of "Flat File". This means that you'll first need to go into Excel and choose Data > Get External Data > Import Text File. Change the file type field to "All Fields (*.*)" and you'll be able to import any flat file using Excel's import wizard. SAVE as an XLS file and now you can import it with the Management Studio wizard.

Caveats:

#1) Personally, in the last step of the Excel import wizard, I always select every column and change them to the TEXT data type simply because I've seen Excel mishandle a lot of data with it's "general" data type. We have an item number in our database called MAR-13 and this looks like a date to Excel, so it changes it to it's default date setting "13, Mar"...quite a bit different. I've seen it mishandle zip codes (drops the "0" from 04444 because it thinks it's an integer), large numbers like UPC codes get changed to scientific notation, etc. Many other examples exist...trust me, change everything to TEXT and work with the data types once you have them in SQL Server.

#2) If you've never worked with XLS file imports, then be warned...you WILL encounter this next issue eventually. It's not a huge one, but it can be confusing when you first encounter it. The Jet database engine is used during the import process. By default, it uses the first 8 rows of a file to guess what datatype each field is. The problem here is if any row after row-8 has data that doesn't fit into this guessed datatype, the import process will throw an error. For example, the first 8 rows in Column A have text with less than 255 characters, so the import wizard assigns NVARCHAR(255). However, row 9 has 256 characters in Column A. This will cause an error. The solution is here: http://support.microsoft.com/kb/281517 (if that link ever goes dead, search for "jet TypeGuessRows" (no quotes)

HTH,

Gabe Lawrence
You are not authorized to post a reply.

Acceptable Use Policy