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

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

When you move the offending row to the bottom of the worksheet, does the new 'first row after the header' get loaded? — Peter Tirrell
Yes the new first row loads perfectly. The 'old' row loads only when I format the whole of the offending row with the format painter using previous row. I think the problem has to do with how the old first row is started and finished because the bulk copy does not 'see' it. I am unsure how to fix this programmatically and I don't want to have to do this manually for each file I am loading. — Bluffer

Bluffer Bluffer · Accepted Answer · 2015-07-07T10:12:57

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

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

1 Answers