Question
I am able to get sqlbulkcopy working well on iis6(testing server) and iis7(live server) but when uploading the data in iis7, the rows are cut off. It only imports ~190,000 out of 250000 in iis7. In iis 6 using the same code/page it loads all 250000 records without issues. Also both servers are updating the same SQL DB.
I have searched all over for a solution. Any help is much appreciated.
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
Popular Answer
Thanks Andy for the ExcelDataReader tip!
Got it to work with ExcelDataReader. Looks like it was a memory issue with OleDB. Posting my code in case it will help others in 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
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why