script task bulkcopy from Excel to Sql Server 2008. Missing first row after the header

Question

I am currently loading data from an Excel worksheet which has a header to a table in SQL server. I am using a script task in SSIS. Everything is working fine except that it doesn't load the first row after the header. If I move the row to the bottom of the worksheet it loads correctly.

The following is the code I am using:

string excelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullPath + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";

OleDbConnection oledbconn = new OleDbConnection(excelconnectionstring);
Dts.Variables["User::FileLoaded"].Value = false;

try
{
    OleDbCommand oledbcmd = new OleDbCommand(exceldataquery, oledbconn);
    oledbconn.Open();
    OleDbDataReader dr = oledbcmd.ExecuteReader();


    SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlconnectionstring);
    bulkcopy.BatchSize = 1000;
    bulkcopy.DestinationTableName = sqltable;

    while (dr.Read())
    {
        bulkcopy.WriteToServer(dr);
    }

    Dts.Variables["User::FileLoaded"].Value = true;
}
catch (Exception e)
{
    MessageBox.Show(e.Data + " " + e.InnerException + " " + e.Message + " " + e.Source);           
    Dts.Variables["User::FileLoaded"].Value = false;
}
finally
{
    oledbconn.Close();
    Dts.TaskResult = (int)ScriptResults.Success;
}

Answer 1

Ok so I solved this by changing the method I was employing. Now everything works perfectly.

I used a data adaptor instead as follows. I still don't know why previous code didn't work correctly

OleDbDataAdapter adapter = new OleDbDataAdapter(exceldataquery, oledbconn); adapter.Fill(dataTable);

            SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlconnectionstring);
            bulkcopy.BatchSize = 1000;
            bulkcopy.DestinationTableName = sqltable;

            bulkcopy.WriteToServer(dataTable);