Problem: when using Excel Source task to load excel data into database, if the data type in one column is different, it will appears Null into our database.
Solution:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
using Microsoft.Office.Interop.Excel to process excel file public System.Data.DataTable ReadExcel() { System.Data.DataTable dt = new System.Data.DataTable("tmp"); Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass(); app.Visible = false; Workbook workbook = app.Workbooks.Open("aa.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); var sheet = workbook.Sheets[1] as Worksheet; try { var c = new DataColumn("a", typeof(int)); c.AutoIncrement = true; dt.Columns.Add(c); for (int i = 1; i <= sheet.UsedRange.Rows.Count; i++) { DataRow dr = dt.NewRow(); for (int j = 1; j <= sheet.UsedRange.Columns.Count; j++) { Range range = sheet.Rows[i, Missing.Value] as Range; if (i == 1) { var header = (range.Cells[Missing.Value, j] as Range).Text.ToString(); var type = string.Empty; if (j == 1 || j == 66) { dt.Columns.Add(header, typeof(DateTime)); } else if (j == 65) { dt.Columns.Add(header, typeof(int)); } else { dt.Columns.Add(header, typeof(string)); } } else { var v = (range.Cells[Missing.Value, j] as Range).Text.ToString(); if (j == 1 || j == 66) { if (string.IsNullOrEmpty(v)) { dr[j] = DBNull.Value; } else { dr[j] = DateTime.Parse(v); } } else if (j == 65) { if (string.IsNullOrEmpty(v)) { dr[j] = DBNull.Value; } else { dr[j] = int.Parse(v); } } else { dr[j] = v; } } } if (i > 1) { dt.Rows.Add(dr); } } } catch (Exception ex) { throw ex; } finally { System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); } return dt; } public void InsertIntoTable(System.Data.DataTable dt) { try { string strCon = "Integrated Security = ;Data Source=;Initial Catalog=;"; SqlConnection connection = new SqlConnection(strCon); connection.Open(); SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection); sqlBulkCopy.DestinationTableName = ""; sqlBulkCopy.WriteToServer(dt); } catch (Exception ex) { throw ex; } } }