Excel import gives NULL rows with DTS Sometimes

when the excel column contains numeric and text data, and you try to import the excel data using say DTS, it will give you either the text values or the numeric values and return NULLs for the other. This is a known problem in excel as it scans rows ( based on the set count, which can be 1 ), and determines the datatype for that column. Any row which does not adhere to that guessed datatype, gets imported as NULL for that column. A solution to this problem as shown by the knowledge base is to add the option IMEX=1; in the connection string. If you use something like DAO to open the excel data, then you can write as according to the MS knowledge article ID 194124 : Set Db = OpenDatabase("C:/Temp/Book1.xls", _ False, True, "Excel 8.0; HDR=NO; IMEX=1;") But the problem is that if we are using DTS, and the excel connection object is opening the connection. So there is no code present as shown above in the package. But fortunately, there is a place where we can modify it. In the excel connections properties ( visible either thru disconnected edit, or a dynamic property task or thru the API functions ), there is a key called OLE DB Properties -> Extended Properties. Add the setting IMEX=1; to the existing value and your problem should be solved. You will be able to import the entire excel sheet data thru DTS without worrying about missed values. This setting puts the mode of excel as import, and retreives all values as text instead of doing a data type guessing.