Workaround for NULL when reading Excel file

Reading an Excel file often occurs when you read certain fields with null values, because Excel takes the data type of the first row as a reference when you read the file, and there are problems if the back is inconsistent.

Following a friend's solution, his Excel file's connection string is written as follows. It is said that the data can be coerced into a string, specifically I did not test again. For reference only.

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";

Where the red part is noteworthy, HDR and IMEX must be used in conjunction, HDR is to use the first row as the data or column name, as the data with the Hdr=no, as the column name with the Hdr=yes, through imex=1 to the mixture as text read, avoid null value. Excel connection in SSIS is not the same as other connection, users do not need to specify the data type, Excel driver can do a sample first, and then based on the results to guess the data type.