Hi, I am trying to load excel data in SQL database through SSIS. I am using sql 2005 and vs 2005. I am getting null values in few rows of 1 column. The data actually is not constant in column. It has values like 8483.60 8483.60 8483.60 8483.60 8483.60 8483.60 7325.99 7325.99 7325.99 7325.99 Then again it has 8413.91.90.80 8413.91.90.80 8413.91.90.80 8413.91.90.80 8413.91.90.80 8413.91.90.80 8413.91.90.80 8413.91.90.80 8413.91.90.80 8413.91.90.80 8413.91.90.80 8413.91.90.80 8413.91.90.80 8413.91.90.80 So for 2nd set of values it is showing null values. I tried nvarchar, varchar, float datatypes but the problem persist. Any solutions please. Thanks

in the connection string property of excel connection manager add the IMEX=1 as below: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\..\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Hi, Thanks for quick reply. I really appreciate your help. I think it looks lame for you but I am a beginner, I searched for the connection property and could not find it.Where should i write it. Can you please specify location. Thanks

in the connection managers pane in package designer, right click on the excel connection manager, and select properties then in properties window you will find the ConnectionString propertyhttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

I did the changes as you said, but again I am getting null values for those rows. Do I need to change something in datatype. Can you please sugegst which datatype should work for column with such data( given in question). I can change datatype of column in table.

just replace of of 8413.91.90.80 values with one of first 8 rows of excel file, Jet engine will guess data type of excel column based on first 8 rows, so when all first rows are of type numeric it will consider it as numeric. but if a value like 8413.91.90.80 be there it will consider the data type as string.http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

I dont know the reason, again its showing the same null value though I kept it as 3rd row. I tried using CSV file(By converting it to CSV). Its working, I am getting all values but the problem is its taking the colum headers as 1st row. When I use option "header rows to skip as 1" , No row is getting inserted. How to avoid it. Also one of the columns is getting inserted with double quotations as " US", "INDIA". How to avoid it. Thanks

could you paste sample input row completely here?http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Did you try changing the excel column datatype? Just try it once.Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

If you get null values in some of the rows like this example below, then you have mixed datatypes in your column. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

I have data as given in my question. That is mixed datatype. I tried changing data in excel from float to varchar, nvarchar. I did it through data conversion. But I did not see the result. Please let me know which datatype to use and changing in data conversion is correct? I also tried using CSV file (Flat file source) and my output data is as below: "ItemNmbr " "VendorID " TariffCode "PrefCriterion " CntryOrig 472 4235 8413.9 B "US " 745 4235 8413.91 B "US " 1129 4235 8413.91 B "US " 1157 4235 8413.91 B "US " 1158 4235 8413.91 B "US " 1191 4235 8413.91 B "US " 1201 4235 8413.91 B "US " 1406 4235 8413.91.123 B "US " The problem here is As you can see, I am getting column headings as first row, though their are colum headings of table . So its getting repeated twice. When i used the option header row to skip as 1 I am not getting any data. And also the countryorig data is in double quotations which I do not want it. Also in last line you can see data changing. Thiscreates problem with excel source. They get null Any help regarding Excel source or CSV source would be appreciated. Thanks

Hi, I am able to remove header columns. I used the option "Column headings in first row" and it worked for me. Regarding double quotaions in last column. I made changes in flat file connection manager properties. Their is text qualifier which I chnaged from none to ". It works.So simple!! I dont know why excel has problem and I could not do through it. Thanks