In the past, when reading an Excel file, some fields were often read as null values. In fact, some fields were actually null values, because when reading the file, Excel would take the data type of the first row as a reference, if they are different, some problems may occur.
The following is a solution. The connection string of the Excel file is written as follows. It is said that the data can be forced to be a string. I did not test it again. For reference only.
String strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + Spath + "; extended properties = 'excel 8.0; HDR = yes; IMEX = 1 '";
The red part is worth noting. HDR and IMEX must be used together. HDR indicates whether to use the first row as data or as a column name and use HDR = No as data, use HDR = yes as the column name; Use IMEX = 1 to read the hybrid type as the text type to avoid null values. The Excel connection in SSIS is different from other connections. You do not need to specify the data type. The Excel driver can sample the data first and then guess the data type based on the result.