Hello: I have tried to import data from Excel 2003 to Sql Server 2005 using the Import Wizard to no avail. Here is my issue: Excel column hasAfter import, database table these value sets: has these correspondingvalue sets: 1.01 null 1.02 null 1.04 1.04 1.05 1.05 1.06 null 2.071 2.0710000000000002 2.0723 2.0230000000000001 2.084 2.0840000000000001 2.084A null 2.084Bnull In Excel, I formatted the column as text. In the Sql server 2005 database table, the resulting column is defined as varchar(50) null. Why am I getting these result? I am expecting to get the source data as text. Any help is appreciated. JC

That looks odd. Can you try this? Save as in Excel 2003 as csv file. Import it again with the SSIS import/export wizard (a new run). Let us know what happens.

Tried it and it failed. I saved the spreadsheet as a .csv file, thentried to import itas a "flat file source" to sql server 2005 via import wizard. First, the mapping of the columnsin the Import wizard looks out of order. Second, it failed to load any record. Here are the error messages: Error 0xc02020f6: Data Flow Task: Column "Description" cannot convert between unicode and non-unicode string data types.(SQL Server Import and Export Wizard)Error 0xc02020f6: Data Flow Task: Column "Comment" cannot convert between unicode and non-unicode string data types.(SQL Server Import and Export Wizard)Error 0xc004706b: Data Flow Task: "component "Destination - Test" (134)" failed validation and returned validation status "VS_ISBROKEN".(SQL Server Import and Export Wizard)Error 0xc004700c: Data Flow Task: One or more component failed validation.(SQL Server Import and Export Wizard)Error 0xc0024107: Data Flow Task: There were errors during task validation.(SQL Server Import and Export Wizard)

Are you importing into a BRAND NEW table? Are you up-to-date with service packs? SP2?

I was importing to an existing table. I also tried importing to a new table. However, they bothdidn't work. Yes, I have service pack 2 on sql server 2005. Here is what I have: Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) Thus far, importing the excel spreadsheet andsaving the spreadsheet as .csv file before importing data to SQL Server 2005, still do not work. This appears to be a bug on the import wizard.

Can you do the following? 1. Just test it with the single column you listed in your opening post. Put the name of column into the first cell as ALPHA 2. Run the SSIS Import/Export wizard 3. Select sheet1 and assign OMEGA to destination table in tempdb 4. After you enter destination (new table) you have a chance to preview the data. Make sure it is correct. 5. Finish and run. It created nvarchar(255) column for me. everything was working. Wonder if this is 64-bit related......

I searched via Google on the issue of my opening post and still couldn't find any solution. I triedyour suggestionand didn't work as expected. When I checked the EDIT MAPPING on Import Wizard, the resulting field is defined as FLOAT data type. Here is the value sets in EXCEL and resulting SQL Server table. In Excel, IAfter IMPORT to Sql Server, createdI got these result these columns Alpha 1.01 1.01 1.02 1.02 1.04 1.04 1.05 1.05 1.06 1.06 2.071 2.071 2.0723 2.0723 2.084 2.084 2.084A NULL 2.084B NULL

MyBlog wrote: When I checked the EDIT MAPPING on Import Wizard, the resulting field is defined as FLOAT data type. That makes sense. You cannot transfer 1000A or A1000 into FLOAT. If you change the type to NVARCHAR(32), it will work. The wizard looks at the top of column data set to make a data type recommendation. So you have to make sure that you have characteristic data in the first few rows. If this is not possible, you can change the data type manually. Let us know if helpful.

Hi Buddy, Data type from float to nvarchar we cant convert. First of all, excel source component is getting datatype from first 5 row values. So, do onething, Add 5 blank rows and you already converted the numbers to text in Excel source file (how you converted the numbers, If you changed the format into "Text" means that will not work. so add ' in front of the numbers).

I manually changed the type to nvarchar(255) and still I am not getting the expected result. In Excel, I After IMPORT to Sql Server,have I got these resultthese columns Alpha 1.01 1.011.02 1.021.04 1.041.05 1.051.06 1.06000000000000012.071 2.07100000000000022.0723 2.07229999999999982.084 2.08400000000000012.084A NULL2.084B NULL I am expecting to get the source data as is after the import to SQL Server 2005 table. Yet, I am getting floating point numbers and NULLs in the table. Are there settings I missed in EXCEL and Sql server? I think I tried them all. This definitely looks like a bug in sql server 2005 import.I am currently using TOAD and it did the job but would be good if SQL Server 2005 import works as expected.

Hi Buddy, I already told you, that in SSIS, excel source component picking the datatype based on 1st 5 or 10 row values. Assume one of the column in excel file is having data like below, Alpha Alpha1 Alpha2 Alpha3 Alpha4 100 1001 10.1 Then excel source component treate the above column datatype as string, if the numbers are coming in first 5 rows and if textvalues follows the numbers then the datatype of the column is double precission float. So, If you want to load the numbers as text means, Add few blank rows or give some text values in first 5 rows(Later you can filter those text values using conditional split component) then convert the numbers into text by adding ( ' ) in front of the numbers like '100 or '100.1. then load. Thanks, and regards,

Yes, I see whatyou are saying. But, I am getting multiple files from users and it is just not the right way to add rows with strings into the files before importing them to the database tables. And then having to delete them from the table when the import is done. I have also set my registry to scan all the values for that column inEXCEL to determine the proper datatype before the import. But it still didn't work. Here is what I changed in registry. Go to HKEY_LOCAL_MACHINE/SOFTWARE/MICOROSOFT/JET/4.0/ENGINES/EXCEL and set TypeGuessRows to decimal 0. Microsoft, can you take a look at this issue? Service pack fixes?? Any help is appreciated.

MyBlog wrote: it is just not the right way to add rows with strings into the files before importing them to the database tables. And then having to delete them from the table when the import is done. Agreed. However, in production environment we frequently have no choice but implement a workaround. Let us know the resolution.

Hmm. Ok. If I have to tweak the import wizard like this, there is a BUG in the Sql Server 2005 Import Wizard. Thanks for your reply.

Here's what's happening: The Jet provider is usinga float type for that column because the majority of the rows in it are floats. Since Jet thinks that the column is float, that's what it tells SSIS. SSIS has no way of knowing that it's not a float. (For the values that don't fit in floats, like 2.084A, Jet just makes those NULL without telling SSIS.) So, this isn't a problem with SSIS, exactly, it's a problem with Jet. Now, Jet's sort of complicated to use, correctly. Here's a good blog post on the subject: http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/ Here're some pages at MS to give you some specific instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;257819 http://support.microsoft.com/kb/189897/EN-US/ What you need to do is change the TypeGuessRows registry entry (probably to 0), and add the string IMEX=1 to the connection string for the Excel connection manager. I assume you can follow the instructions on how to update the TypeGuessRows registry entry. (While you're there, make sure the ImportMixedTypes value is Text.) For the Excel connection manager change, you can either create your package in the wizard, save it, and the edit that package in BIDS, or you can just create the package in BIDS. Either way, once you've got it, open it up and select the Excel connection manager. In the properties window, edit the connection string, adding IMEX=1 into theExtended Properties list. E.g.:Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1"

Just setting IMEX = 1 should solve your problem. The reason is unnecessary assumption of Jet Engine. But do check the registry entry ImportMixedTypes is set to 'Text' (withoug quotes). Butthis appliesonly under the circumstances whenthere is mixed data. In that case thecolumn is imported as Text. Ifthe column does not have mixed data then this does not apply.Setting IMEXvalue is just asking Jet Engine to honour the registry settings.

Here is what I did to get around a similar issue that I was having 1) Export the excel data sheet to a csv file 2) Change the csv extension to txt 3) Open the txt file in Excel and use the Import Wizard to change all columns to Text 4) Now Save the file as a Normal excel file and import it using SSIS Import Wizard

Chiming in late. I have a similar issue. After reading this thread, I resorted to importing the Excel sheet into Access first. Access import wizard allows you to force the datatype. I had several Floats, which Access tried to import as Double (Which probably would work anyway), but I was able to force them to text. Then I imported from Access to SQL and preserved all my data.