Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Sporadic failure on SF_TableLoader

I am using DBAmp 3.7.3 and am performing the following insert statement:

EXEC SSE_STAGING.dbo.SF_TABLELOADER 'INSERT:bulkapi,batchsize(2000)','SALESFORCE_MIG','Case_Comment__c_Load'

There are about 1.2 million rows in this table. Only about 6 columns... none of them very big.

The issue is that about 1 in 4 times I run this statement, it completely bombs before ever starting the batch in Salesforce. The error message is pasted below.

The notable part is a reference to me is near the bottom... "Invalid object name 'Case_Comment__c_Load_Result'. This leads me to believe it is having a problem creating the CSV file on the working directory. That drive has a ton of space and like I said, it works most of the time, just not all of the time. I can actually re-run the same statement that failed, and it may fail another one or two times, and then finally work.

Size of the insert doesn't seem to be an issue... I have seen this with a table that only has 10K rows.

Any ideas?

- Robert

------------------------------------------------------------------------------------------------------------

--- Starting SF_TableLoader for Case_Comment__c_Load V3.7.3
04:37:05: Run the DBAmpNet2.exe program.
04:37:06: DBAmpNet2 3.7.3.0 (c) Copyright 2015-2017 forceAmp.com LLC
04:37:06: Batch size reset to 2000 rows per batch.
04:37:06: Parameters: insert Case_Comment__c_Load WRTL3HJDZ SSE_Staging SALESFORCE_MIG
04:37:06: Using the Salesforce BulkAPI.
04:37:06: Sort column will be used to order input rows.
04:40:07: Error: System.InvalidOperationException: Unable to build the 'Columns' collection because execution of the SQL query failed. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout pe
riod elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TrySetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParamet
erEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolea
n inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.ProviderBase.DbMetaDataFactory.ExecuteCommand(DataRow requestedCollectionRow, String[] restrictions, DbConnection connection)
--- End of inner exception stack trace ---
at System.Data.ProviderBase.DbMetaDataFactory.ExecuteCommand(DataRow requestedCollectionRow, String[] restrictions, DbConnection connection)
at System.Data.ProviderBase.DbMetaDataFactory.GetSchema(DbConnection connection, String collectionName, String[] restrictions)
at System.Data.SqlClient.SqlConnection.GetSchema(String collectionName, String[] restrictionValues)
at DBAmpNet2.DataReaderExtension.ToCsvIdFirst(IDataReader bulkOpsDataReader, String fileName, Boolean includeHeaderAsFirstRow, DescribeSObjectResult objDescribe, String operation, String SFObjectName, String TableToLoad, SqlConnection sqlConn, String e
xternalIdField, List`1& fieldNamesToDrop, Dictionary`2& fkFieldNamesDictionary, Boolean useUTC, Int32& totalCount)
at DBAmpNet2.LoaderBulkAPI.CreateCsvFile()
at DBAmpNet2.Program.HandleBulkOpsOldBulk(String currentOperation, Options currentOptions, DBAmpRegistry currentDBAmpRegistry)
at DBAmpNet2.Program.Main(String[] args)
04:40:07: DBAmpNet2 Operation FAILED.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Case_Comment__c_Load_Result'.
04:40:08: Error: DBAmpNet2.exe was unsuccessful.
04:40:08: Error: Command string is C:\"Program Files"\DBAmp\DBAmpNet2.exe BulkOpsOldBulk "INSERT:bulkapi,batchsize(2000)" "Case_Comment__c_Load" "WRTL3HJDZ" "SSE_Staging" "SALESFORCE_MIG" " "
--- Ending SF_TableLoader. Operation FAILED.
Msg 50000, Level 16, State 1, Procedure SF_TableLoader, Line 338
SF_TableLoader Error: 04:37:06: DBAmpNet2 3.7.3.0 (c) Copyright 2015-2017 forceAmp.com LLC04:37:06: Batch size reset to 2000 rows per batch.04:37:06: Parameters: insert Case_Comment__c_Load WRTL3HJDZ SSE_Staging SALESFORCE_MIG04:37:06: Using the Salesforce BulkAPI.04:37:06: Sort column will be used to order input rows.04:40:07: Error: System.InvalidOperationException: Unable to build the 'Columns' collection because execution of the SQL query failed. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TrySetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream...
1 person has
this problem
+1
Reply