script task bulkcopy from Excel to Sql Server 2008. Missing row

excel excel-2007 sqlbulkcopy sql-server ssis

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. Any ideas?

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;
        }

Accepted Answer

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);


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why