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

excel excel-2007 sqlbulkcopy sql-server ssis

Question

I'm presently putting data into a SQL server table from an Excel spreadsheet with a header. In SSIS, I'm utilizing a script task. Except for the fact that it doesn't load the first row following the header, everything is perfect. The row loads properly if I relocate it to the worksheet's bottom. Any thoughts?

The code I'm using is as follows:

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

OleDbConnection New: oledbconn OleDbConnection(excelconnectionstring);\sDts.Variables["User::FileLoaded"] . False value;

        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;
        }
1
0
6/26/2015 2:25:43 PM

Accepted Answer

Okay, I changed my approach and was able to resolve issue. Everything is now flawless.

Instead, I did the following using a data adapter. I still don't understand why the earlier code didn't function properly.

adapter = new OleDbDataAdapter ExcelDataQuery, OleDbConn; OleDbDataAdapter; adapter Fill(dataTable);

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

            bulkcopy.WriteToServer(dataTable);
0
7/7/2015 10:12:57 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow