hi,

I have 2 questions:

when I am taking the data from Excel to SQL using SSIS  

1) In my Excel one column have first 113 rows as blank and then it has numeric value this column is giving an error potential loss of data. So, I make the fist row as 0 and it worked fine. But, the data is entered by the user in the future .. how can I resolve this issue with out entering 0 in the first row of the column .

2) In the same excel there are other 2 columns only fist 34 rows are blank .. they are working fine. how many rows will SSIS check to decide the column data type.  

please advice

thanks

Use a Derived column and expression like below (Replace Column_Name to actual ColumnName)

LEN(TRIM([Column_Name]))==0 ? NULL(DT_WSTR, 10) : [Column_Name] 

You need to modify TypeGuessRows registry entry for this

ALso try setting IMEX = 1 in connection string

see

http://microsoft-ssis.blogspot.com/2011/06/mixed-data-types-in-excel-column.html

1) -->  It happens because SSIS tries to identify the data type by just looking at first 8 rows and it finds that if it is blank then it assign some data type with less length and then in reality you have numbers and hence throws error.

Set IMEX = 1 in connection string.
Set TypeGuessRows setting to 0 from RegEdit.

HKEY_LOCAL_MACHINE -> SOFTWARE -> Wow6432Node -> Microsoft -> Jet -> 4.0 ->Engines -> Excel -> TypeGuessRows has the value 0.

2) --> By default it checks 8 rows. You may check above setting from Registry Editor.

Please refer:

http://blog.concentra.co.uk/2013/05/15/why-ssis-always-gets-excel-data-types-wrong-and-how-to-fix-it/

http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/mssqlserver/what-s-the-deal-with/

thanks for reply .. could you please explain me how to set IMEX = 1 in connection string ?