Hello Sir,

I have a excel record of 10 Lacks and import that excel data to sql server.read excel using closed xml and insert data using 

sqlbulkcopy but i found exception on bulkcopy please see it.

error in line 53

An unhandled exception occurred while processing the request.

Unknown location

System.Data.SqlClient.SqlConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction)

---- thsi error-----

using (XLWorkbook workBook = new XLWorkbook(path))
{
    IXLWorksheet workSheet = workBook.Worksheet(1);
    DataTable dt = new DataTable();
    bool firstRow = true;
    foreach (IXLRow row in workSheet.Rows())
    {
        if (firstRow)
        {
            query += "IF OBJECT_ID('dbo." + Path.GetFileNameWithoutExtension(file.FileName) + "', 'U') IS NULL ";
            query += "BEGIN ";
            query += "CREATE TABLE [dbo].[" + Path.GetFileNameWithoutExtension(file.FileName) + "](";
            foreach (IXLCell cell in row.Cells())
            {
                dt.Columns.Add(cell.Value.ToString());
                using (SqlConnection con2 = new SqlConnection(_connectionString))
                {
                    SqlCommand cmd2 = new SqlCommand("spAddColumn", con2);
                    cmd2.CommandType = CommandType.StoredProcedure;
                    cmd2.Parameters.AddWithValue("@ColumnName", cell.Value);
                    con2.Open();
                    cmd2.ExecuteNonQuery();
                    con2.Close();
                }
                query += cell.Value.ToString() + " VARCHAR(MAX),";
            }
            firstRow = false;
            query = query.TrimEnd(',');
            query += ")";
            query += " END";
        }
        else
        {
            dt.Rows.Add();
            int i = 0;
            foreach (IXLCell cell in row.Cells())
            {
                dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                i++;
            }
        }
    }
    //string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con1 = new SqlConnection(_connectionString))
    {
        SqlCommand cmd = new SqlCommand(query);
        cmd.Connection = con1;
        cmd.CommandTimeout = 60;
        con1.Open();
        cmd.ExecuteNonQuery();
        SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con1);
        sqlBulkCopy.DestinationTableName = "dbo." + Path.GetFileNameWithoutExtension(file.FileName);
        sqlBulkCopy.WriteToServer(dt);
        con1.Close();
    }
}