DataSync

I am receiving an "Impossible to Load Data to Table" error message.

When attempting a Load All operation, some users receive the following error message:

Error Message

LoadAll completed with errors: Impossible to load data to <Customers>., InnerMessage: Impossible to load data to <Customers>. -> Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. Le fichier spécifié est introuvable.

2020-08-06 10:56:23.041 -04:00 [ERR] Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. Le fichier spécifié est introuvable. System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. Le fichier spécifié est introuvable. File name: 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' at System.Reflection.RuntimeAssembly.nLoad(AssemblyName fileName, String codeBase, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, IntPtr ptrLoadContextBinder) at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, IntPtr ptrLoadContextBinder) at System.Reflection.Assembly.Load(AssemblyName assemblyRef) at System.Data.SqlClient.SqlConnection.ResolveTypeAssembly(AssemblyName asmRef, Boolean throwOnError) at System.TypeNameParser.ResolveAssembly(String asmName, Func`2 assemblyResolver, Boolean throwOnError, StackCrawlMark& stackMark) at System.TypeNameParser.ConstructType(Func`2 assemblyResolver, Func`4 typeResolver, Boolean throwOnError, Boolean ignoreCase, StackCrawlMark& stackMark) at System.TypeNameParser.GetType(String typeName, Func`2 assemblyResolver, Func`4 typeResolver, Boolean throwOnError, Boolean ignoreCase, StackCrawlMark& stackMark) at System.Type.GetType(String typeName, Func`2 assemblyResolver, Func`4 typeResolver, Boolean throwOnError) at System.Data.SqlClient.SqlConnection.CheckGetExtendedUDTInfo(SqlMetaDataPriv metaData, Boolean fThrow) at System.Data.SqlClient.SqlDataReader.GetValueFromSqlBufferInternal(SqlBuffer data, SqlMetaData metaData) at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at DBProviders.BaseProvider.RecordReaderOfArrayOfFastExpando(IDataReader reader, List`1 metadata) at DataSyncQueryExecutor.BaseDataSyncQueryExecutor.ProduceFromReader[T](DbTranslateResult translateResult, ITargetBlock`1 consumerHeadBlock, CancellationToken cancellationToken) at DataSyncQueryExecutor.BaseDataSyncQueryExecutor.ProduceForLoadAll[T](SqlReadableForDataLoad sqlObject, ITargetBlock`1 consumerHeadBlock, CancellationToken cancellationToken) at DataSyncQueryExecutor.ExecutorExtensions.ProduceForLoadAll[T](ConnectionDefinitionForDataLoad connectionDetails, SqlReadableForDataLoad sqlObject, ITargetBlock`1 consumerHeadBlock, CancellationToken cancellationToken) at DataSynchronizer.SqlObjectForSynchronization.<>cDisplayClass3_0.<<LoadAll>b_0>d.MoveNext() — End of stack trace from previous location where exception was thrown — at DataSynchronizer.BaseSqlObjectSynchronizer.ProduceAndConsume(Func`2 producerCallback, JobAction actionType, CancellationToken cancellationToken) at DataSynchronizer.SqlObjectForSynchronization.LoadAll(CancellationToken cancellationToken)

Solution

These errors are caused by a database type mismatch in one of your table fields. To resolve the issue, simply remove the checkmark in the Sync column next to the problematic table (it will have a red X in the Type Destination column as shown in the image below). When re-attempting your load operation, you should no longer receive the error message.

I am Receiving an "HTTP Error 500.21" Message.

When attempting to use DataSync for the first time after installing it, some users receive the following error message:

Solution

This error is typically happens when users have manually downloaded and installed the wrong version of .NET Core (including the SDK).

To resolve the issue:

Uninstall DataSync as well as all .NET Core packages that you currently have installed.
Restart your server.
Re-install DataSync. The DataSync installation process will automatically install the correct version of .NET Core which should resolve the issue.

See System Requirements for more information on DataSync installation requirements.

I am unable to create a SharePoint connection in DataSync.

Sometimes, users are unable to create SharePoint connections in DataSync if dual-factor authentication is enabled in their configuration.

Solution

Remove dual-factor authentication from your SharePoint configuration by following the steps below:

Log in to SharePoint and navigate to the Account Settings menu.
In the Account Settings menu, click the Additional Security Verification option at the bottom of the Security Info pane.
Select the App Passwords option and create a new password for DataSync. Now, no double authentication will be necessary when logging in to DataSync.

My DataSync UI is looping after attempting to save a Sage 100 ProvideX connection.

After attempting to save a ProvideX connection, the user's UI begins looping and never saves the connection configuration.

Solution

To resolve this issue, you must change your DataSync application pool in the IIS Manager to a Windows or domain user that has access to the shared Sage 100 path.

To do so:

Open the IIS Manager and select Application Pools.
From the Application Pools list, right-click on DataSyncIdentity and select Advanced Settings from the drop-down list (shown below).
Locate the Process Model section and change the application pool identity from Built-in account to Custom account (as shown below). Click OK. This should resolve the UI looping issue in DataSync.
Repeat the above step for all DataSync application pools.

Why are there missing rows when either doing an Incremental Load, Load by Partition or Process Deleted Records?

This occasional discrepancy in data synchronization may arise due to the representation of a Date field as DateTime within DataSync. When comparing two distinct data types, inconsistencies in the dates being compared can result in missing rows during the loading or deletion processes.

Note
To verify whether a field is not correctly cast within DataSync, utilize the "Preview data" feature to inspect if all date entries contain a time value of 0:00:00 across all rows. Alternatively, consult the table documentation to confirm the data type used for the specific field.

Solution

To fix this discrepancy, it is essential to create a custom field explicitly casting the field as a date. Subsequently, employ this customized field as your Tracking Key or Load by Partition field for a seamless data synchronization process.

When loading from a Sage Intacct Source, I am receiving an error message that contains "You do not have permission for API operation READ_BY_QUERY".

When attempting a Load All operation, some users receive the following error message:

Error Message

System.Data.CData.SageIntacct.SageIntacctException (0x80004005): [500] Could not execute the specified command: . You do not have permission for API operation READ_BY_QUERY on objects of type xyz . ---> cta230S.QdY: . You do not have permission for API operation READ_BY_QUERY on objects of type xyz

at cta230S.QTP.LO(String , QdW )

at cta230S.QTP.v()

at cta230S.QTE.ZN(QdW )

at cta230S.FTo.m(Qdn , FTk )

at cta230S.FTk.g(FEk , FTW , FEv , FTW , FTW , String )

at cta230S.FTk.F(Qdn , FTW , FEv , FTW , FTW )

at cta230S.FYn.P(String , Qdn , QdU , QdI )

at cta230S.FYn.bL(Qdn , QdU , QdI )

at cta230S.Qbd.ZN(QdW )

at cta230S.FTo.m(Qdn , FTk )

at cta230S.FYU.F(FYU , FTk )

at cta230S.FYU.yCj(FTk )

at cta230S.FbY.X(FTk )

at cta230S.FbY.g(FbY , FTk )

at cta230S.FbY.yCj(FTk )

at cta230S.FbY.X(FTk )

at cta230S.FbY.g(FbY , FTk )

at cta230S.FbY.yCj(FTk )

at cta230S.FdT.yCh(FTk )

at cta230S.Pdx.fj(QdW , Pds )

at cta230S.FQQ.yyV()

at cta230S.PzW.R(PYx , PoQ )

at cta230S.Pzs.yUx()

at cta230S.PzW.yyw()

at cta230S.FQs.jK(SQLQuery , Int32 , Pgm )

at cta230S.FQs.yQ(IDataStatement , SQLQuery , QueryParameterCollection , Int32 , String , Foc )

at cta230S.FQb.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )

at cta230S.PAI.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )

at cta230S.PYn.yyA(QueryParameterCollection )

Solution

This is a configuration issue in Sage Intacct. The user that is set in DataSync to read the data from Sage Intacct does not have permission to read the specified object. The permission for the user needs to be set in Sage Intacct.

When executing a Load All for an extraction, I am receiving an error message that contains “401 Unauthorized”.

When attempting a Load All operation, some users receive the following error message:

Solution

When calling the API, it is not reachable. It maybe a configuration of the connection within Datasync. This may happen with any API connection such as Sage Intacct, Custom API, etc.

When executing a Load All for a Sage Intacct extraction, I am receiving an error message that contains “The period selected is not valid”.

When attempting a Load All operation, some users receive the following error message:

Error Message

System.Data.CData.SageIntacct.SageIntacctException (0x80004005): [500] Could not execute the specified command: [BL03002069] . The period selected is not valid [Support ID: XiCdvEB033%7EZqp6jP075jv-2UHWG0rrjAAAAAU]. ---> cta230S.QdY: [BL03002069] . The period selected is not valid [Support ID: XiCdvEB033%7EZqp6jP075jv-2UHWG0rrjAAAAAU].

at cta230S.QTP.LO(String , QdW )

at cta230S.QTP.v()

at cta230S.QTE.ZN(QdW )

at cta230S.FTo.m(Qdn , FTk )

at cta230S.FTk.g(FEk , FTW , FEv , FTW , FTW , String )

at cta230S.FTk.F(Qdn , FTW , FEv , FTW , FTW )

at cta230S.FYn.P(String , Qdn , QdU , QdI )

at cta230S.FYn.bL(Qdn , QdU , QdI )

at cta230S.Qbd.ZN(QdW )

at cta230S.FTo.m(Qdn , FTk )

at cta230S.FYU.F(FYU , FTk )

at cta230S.FYU.yCj(FTk )

at cta230S.FbY.X(FTk )

at cta230S.FbY.g(FbY , FTk )

at cta230S.FbY.yCj(FTk )

at cta230S.FbY.X(FTk )

at cta230S.FbY.g(FbY , FTk )

at cta230S.FbY.yCj(FTk )

at cta230S.FdT.yCh(FTk )

at cta230S.Pdx.fj(QdW , Pds )

at cta230S.FQQ.yyV()

at cta230S.PzW.R(PYx , PoQ )

at cta230S.Pzs.yUx()

at cta230S.PzW.yyw()

at cta230S.PzY.yyw()

at cta230S.FQs.jK(SQLQuery , Int32 , Pgm )

at cta230S.FQs.yQ(IDataStatement , SQLQuery , QueryParameterCollection , Int32 , String , Foc )

at cta230S.FQb.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )

at cta230S.PAI.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )

at cta230S.PYn.yyA(QueryParameterCollection )

at

System.Data.CData.SageIntacct.SageIntacctCommand.ExecuteDataReader(CommandBehavior behavior)

at Shared.Objects.ThreadUtils.RetryAsync[T](Func`1 action, TimeSpan retryWait, Int32 retryCount, Boolean increaseRetryWait, Func`2 exceptionPredicate, CancellationToken cancellationToken)

at DBProviders.DbCommandExtensions.GetReaderAsync(IDbCommand command, CommandBehavior commandBehavior, Int32 retryWaitSeconds, Int32 retryCount, CancellationToken cancellationToken, Boolean printParameter)

at

DataSyncQueryExecutor.BaseDataSyncQueryExecutor.ProduceFromReaderWithCount[T](DbTranslateResult translateResult, ITargetBlock`1 consumerHeadBlock, Int32 retryCount, Int32 retryWaitSeconds, CancellationToken cancellationToken)

Solution

This can happen when the period that is set in the extraction is invalid. This error nessage is returned by Sage Intacct.

When executing a Load All for a Sage Intacct extraction, I am receiving an error message that contains “Could not execute the specified command: [Given docparid does not exist]”.

When attempting a Load All operation, some users receive the following error message:

Error Message

System.Data.CData.SageIntacct.SageIntacctException (0x80004005): [500] Could not execute the specified command: [Given docparid does not exist.] . [Support ID: kOgmmEB032%7EZqE78P0r4CpGlkWbLJbnAAAAAo]. ---> cta230K.ujj: [Given docparid does not exist.] . [Support ID: kOgmmEB032%7EZqE78P0r4Cp-GlkWbLJbnAAAAAo].

at cta230K.uXF.Fm(String , ujK )

at cta230K.uXF.Er()

at cta230K.sXO.jF(ujZ , sXd )

at cta230K.sXd.M(sOd , sXg , snW , sXg , sXg , String )

at cta230K.sXd.i(ujZ , sXg , snW , sXg , sXg )

at cta230K.snb.M(String , ujZ , ujT , ujP )

at cta230K.snb.dO(ujZ , ujT , ujP )

at cta230K.ubj.zl(ujK )

at cta230K.sXO.jF(ujZ , sXd )

at cta230K.snK.R(snK , sXd )

at cta230K.snK.tUd(sXd )

at cta230K.sbQ.V(sXd )

at cta230K.sbQ.A(sbQ , sXd )

at cta230K.sbQ.tUd(sXd )

at cta230K.sbQ.V(sXd )

at cta230K.sbQ.A(sbQ , sXd )

at cta230K.sbQ.tUd(sXd )

at cta230K.sjP.tUs(sXd )

at cta230K.gjO.M(ujK , gjj )

at cta230K.suR.ttI()

at cta230K.gZA.e(gnX , gTs )

at cta230K.gZP.tIN()

at cta230K.gZA.ttj()

at cta230K.suv.H(SQLQuery , Int32 , gWc )

at cta230K.suv.tE(IDataStatement , SQLQuery , QueryParameterCollection , Int32 , String , sTT )

at cta230K.suO.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )

at cta230K.giP.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )

at cta230K.gOR.tth(QueryParameterCollection )

at System.Data.CData.SageIntacct.SageIntacctCommand.ExecuteDataReader(CommandBehavior behavior)

at Shared.Objects.ThreadUtils.RetryAsync[T](Func`1 action, TimeSpan retryWait, Int32 retryCount, Boolean increaseRetryWait, Func`2 exceptionPredicate, CancellationToken cancellationToken)

at DBProviders.DbCommandExtensions.GetReaderAsync(IDbCommand command, CommandBehavior commandBehavior, Int32 retryWaitSeconds, Int32 retryCount, CancellationToken cancellationToken, Boolean printParameter)

at

DataSyncQueryExecutor.BaseDataSyncQueryExecutor.ProduceFromReaderWithCount[T](DbTranslateResult translateResult, ITargetBlock`1 consumerHeadBlock, Int32 retryCount, Int32 retryWaitSeconds, CancellationToken cancellationToken)

Solution

The docparid is expected to be set within the connection. Follow the steps in Configure the Connections.

When executing a Load All for an extraction, I am receiving an error message that contains “500”.

Solution

When the error contains the error code 500, it can be because the call made to the API of the source connection was not fully understood by the API. Re-check all the setup of the source connection to be sure the API is called correctly.