May 27, 2008 at 10:35 am
I have an Excel 2003 file that has 2 columns in it and the data looks like so:
col1 col2
====== ======
8/23/07 14332
5/9/07 73598
12/10/07 <400
1/17/07 <50
Using SQL Import/Export wizard (SQL Server 2005), the first column imports fine, but the second column has all the numbers stripped out, except for any record that started with <. So, the imported table looks like so:
col1 col2
====== ======
8/23/07 NULL
5/9/07 NULL
12/10/07 <400
1/17/07 <50
SQL did give col2 a default of nvarchar(255). So, I'm guessing this is why I'm seeing nulls in place of the numbers.
Perplexed, I imported Excel file into MS Access; the result for col2 did contain both numbers and the text strings that started out with <. And MS Access did give a default of text datatype for col2, which is probably why I was able to preserve all the data in MS Access. Then, I imported the MS Access table into SQL Server 2005 and all the data imported fine.
So, can anyone explain to me why I'm seeing different import results when importing from Excel vs importing from MS Access? I don't mind the workaround through MS Access. I just thought that if you told SQL that a certain column was nvarchar that SQL would just import all the data in that column as text, even though there might be numbers in there.
Thanks in advance.:)
May 27, 2008 at 12:21 pm
When SSIS determines the data types from your spreadsheet (barring having true data types in the worksheet) it samples the data. It does a pretty poor job of this. One way of dealing with it is to make sure the first row of data correctly represents the data types you want (put a text string in the column you have text in).
As another option, after you have created the OLE DB Source component, open it with the Advanced Editor and modify the source data types.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply