In iis6, sqlbulkcopy with ASP works, but not in iis7.

iis-6 iis-7 records sqlbulkcopy

Question

On both iis6 (my testing server) and iis7 (my live server), I was able to get sqlbulkcopy to function properly; however, the rows get chopped off when I upload data in iis7. In iis7, only 190,000 out of 250 000 files are imported. With the identical code/page on IIS 6, all 250000 records load without any problems. The same SQL DB is being updated by both servers as well.

I've looked everywhere for a solution. I really appreciate any assistance.

    Sub UpdateData()

    Dim sSQLTable As String = "Table1"
    Dim sExcelFileName As String = savePath
    Dim sWorkbook() As String = GetExcelSheetNames(sExcelFileName)

    'Create our connection strings
    Dim sExcelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sExcelFileName & ";Extended Properties=""Excel 12.0;HDR=YES;"""

    'Execute a query to erase any previous data from our destination table
    Dim sClearSQL = "DELETE FROM " & sSQLTable
    Dim SqlConn As SqlConnection = New SqlConnection(ConnString)
    Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn)
    SqlConn.Open()
    SqlCmd.ExecuteNonQuery()
    SqlConn.Close()

    'Series of commands to bulk copy data from the excel file into our SQL table
    Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
    OleDbConn.Open()

    Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM [" & sWorkbook(0) & "]"), OleDbConn)
    Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
    Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(ConnString, SqlBulkCopyOptions.UseInternalTransaction)
    bulkCopy.BulkCopyTimeout = 2000
    bulkCopy.DestinationTableName = sSQLTable
    'DEMO bulkCopy.ColumnMappings.Add("Excel", "SQL")
    bulkCopy.ColumnMappings.Add("Material", "Material")
    bulkCopy.ColumnMappings.Add("Plnt", "Plant")
    bulkCopy.ColumnMappings.Add("SLoc", "SLoc")
    bulkCopy.ColumnMappings.Add("S", "S")
    bulkCopy.ColumnMappings.Add("Batch", "Batch")
    bulkCopy.ColumnMappings.Add("Special Stock Number", "SpecialStockNumber")
    bulkCopy.ColumnMappings.Add("Material Description", "MatDesc")
    bulkCopy.ColumnMappings.Add("Typ", "Type")
    bulkCopy.ColumnMappings.Add("StorageBin", "StorageBin")
    bulkCopy.ColumnMappings.Add("Available stock", "AvailStock")
    bulkCopy.ColumnMappings.Add("BUn", "BUn")
    'bulkCopy.ColumnMappings.Add("GR Date", "GRDate")
    bulkCopy.WriteToServer(dr)
    OleDbConn.Close()
End Sub
1
1
12/12/2011 9:34:20 PM

Popular Answer

I appreciate Andy's ExcelDataReader suggestion.

Zzz-9-Zzz made it work. It seems like OleDB has a memory problem. I'm sharing my code in case it might assist someone else in the future.

    Sub updateData()
    Dim sSQLTable As String = "Table1"

    'Execute a query to erase any previous data from our destination table
    Dim sClearSQL = "DELETE FROM " & sSQLTable
    Dim SqlConn As SqlConnection = New SqlConnection(ConnString)
    Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn)
    SqlConn.Open()
    SqlCmd.ExecuteNonQuery()
    SqlConn.Close()


    Dim stream As FileStream = File.Open(savePath, FileMode.Open, FileAccess.Read)

    '1. Reading from a binary Excel file ('97-2003 format; *.xls)
    'Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateBinaryReader(stream)

    '2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
    Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream)

    '3. DataSet - The result of each spreadsheet will be created in the result.Tables
    'Dim ds As DataSet = excelReader.AsDataSet()

     '4. DataSet - Create column names from first row
    excelReader.IsFirstRowAsColumnNames = True
    Dim ds As DataSet = excelReader.AsDataSet()
    Dim sourceData As New DataTable()
    sourceData = ds.Tables(0)

    ''5. Data Reader methods
    'Using destinationConnection As New SqlConnection(ConnString)
    ' open the connection

    'destinationConnection.Open()
    'Using bulkCopy As New SqlBulkCopy(destinationConnection.ConnectionString)

    ' column mappings
    Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(ConnString, SqlBulkCopyOptions.UseInternalTransaction)
    bulkCopy.BulkCopyTimeout = 2000
    bulkCopy.DestinationTableName = sSQLTable

    'DEMO bulkCopy.ColumnMappings.Add("Excel", "SQL")
    bulkCopy.ColumnMappings.Add("Material", "Material")
    bulkCopy.ColumnMappings.Add("Plnt", "Plant")
    bulkCopy.ColumnMappings.Add("SLoc", "SLoc")
    bulkCopy.ColumnMappings.Add("S", "S")
    bulkCopy.ColumnMappings.Add("Batch", "Batch")
    bulkCopy.ColumnMappings.Add("Special Stock Number", "SpecialStockNumber")
    bulkCopy.ColumnMappings.Add("Material Description", "MatDesc")
    bulkCopy.ColumnMappings.Add("Typ", "Type")
    bulkCopy.ColumnMappings.Add("StorageBin", "StorageBin")
    bulkCopy.ColumnMappings.Add("Available stock", "AvailStock")
    bulkCopy.ColumnMappings.Add("BUn", "BUn")
    'bulkCopy.ColumnMappings.Add("GR Date", "GRDate")

    'bulkCopy.DestinationTableName = sSQLTable
    bulkCopy.WriteToServer(sourceData)

    '6. Free resources (IExcelDataReader is IDisposable)
    excelReader.Close()
End Sub
2
12/13/2011 4:28:01 PM


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