INT PH/Cloud Info: Common Integration Errors

Error occurred saving changes: An item with the same key has already been added

This error hints to one of the followings:

• The module name already exists
• A module attribute you created already exists
• You have a column called Item in source data. (This is an open item in dev)
• You have created columns called Source, CreatedBy, UpdatedBy, CreatedOn, UpdatedOn which are default system columns
• In BC this occurs when the name column has a duplicate despite the dimension being unique in BC. The Solver application uses the name column to create the dimension (BC_Issue001).

Rename the duplicate names to ensure all objects are unique.

Cannot insert duplicate key row in object ‘meta.ModuleAttribute’ with unique index ‘UIX_ModuleAttribute_ModuleId_Label’. The duplicate key value is (2, Credit). The statement has been terminated.

Invalid column name ‘UDF002’ (or any UDFs)

Duplicate attribute labels

Cannot drop the table 'f_Trans_GL_temp', because it does not exist or you do not have permission. There is already an object named 'f_Trans_GL_temp' in the database.

Multiple integrations are hitting the same target table at the same time

(This error will be gone in the near future as our engineers work to drop temp tables after each integration run)

Space out the integrations with at least a 5 minute interval, or however long the previous integration needs to complete the process.

Object reference not set to an instance of an object

Connection to the data source may have been lost

Verify Source Object can render in Task Manager.

Gateway:

1. Ensure Gateway OnPrem Service is running
2. Verify web service user is valid

Cloud ERPs:

• Verify the web service user can access the ERP system

Timeout expired after 240 Minutes

1. Connection to the Gateway server is lost
2. A process on the database/Gateway server is “hanging” the communication to the database
3. Insufficient resources on Gateway server to load a large data set

1. Verify the web service user can connect to the database
2. Check that at the time the integration was ran, no process intensive processes was running in the background on the same server
3. Verify transaction counts the integration is trying to load, add a Period filter to limit the data size

Error connecting. Either there is no data source selected, or the Gateway isn’t running.

Connection lost

1. Verify the web service user can connect to the database
2. Check that at the time the integration was ran, no process intensive processes was running in the background on the same server
3. Verify transaction counts the integration is trying to load, add a Period filter to limit the data size

Gateway error:
SignalRAdaptor.Extract took longer than 30 minutes for batch.

• Data set is too large and taking a long time to fully query the table/view in the database
• Consumption of data takes more than 30 min

• Add indexes to the transactional table
• Insufficient resources on the machine where Gateway is resided

Violation of UNIQUE KEY constraint ‘UQ_fTrans_OT12’. Cannot insert duplicate key in object ‘dbo.f_Trans_OT12’. The duplicate key value is (……..). Line 4.

Data not unique

Ensure each transaction is unique.

• Are they using enough dimensions to define uniqueness for transactions?
• Are they using TransactionID?

Cannot insert the value NULL into column 'Account', table …..dbo.f_Trans_GL'; column does not allow nulls. INSERT fails. Line 4

Source data has a NULL/blank/yet to exist in the DW value in the dimension being referenced

Run the dimension load prior to running the module load to ensure all dim codes are up-to-date in the DW.

Converting data type nvarchar to bigint

This error occurs when a dimension that exists within your transactional data does not exist in a respective dimension

1. Rerun your dimension load
2. If this does not resolve your issue, check that your source data has all the proper data, update where needed. 

Error converting data type nvarchar to numeric

This error occurs for any of your numeric mappings when the data provided is not a valid number. Solver support 12 characters to the left of the decimal and 16 characters to the right of the decimal. 

For example, if the source system provide 1.02E17 as a value, this value is invalid because "E" is not a number and will error out. 

1. Ensure that there are no exponential values coming in. This is a SQL feature to convert values to exponential for people who use SQL views.
2. Ensure that the value is no larger than 12 values to the left of the decimal and 12 values to the right of the decimal. For non-SQL ERPs we recommend using the DECIMAL function in the expression builder.
3. In rare cases, we have seen where making the value only 4 decimal places, has resolved this issue as well.

This error will occur when the data passed off to your datetime attribute is not a valid datetime. 

For example: datetime should be in your regional setting (US MM/DD/YYYY). If the date you passed off is 13/01/2020, the month 13 is invalid and therefore you will receive this error message

Conversion failed when converting date and/or time from character string.

Typically referring an incorrect datetime/date/Period field

Ensure Period is in YYYYMMDD.

Date in DW accepts datetime or Date data type.

String or binary data would be truncated. The statement has been terminated.

Source data exceeds the character limit target field can accept

Long Text in DW accepts up to 512 characters, a value passes this limit will result in this error.

1. On the source data, shorten the length of the characters in that column. 
2. Since you cannot change the data type of an attribute, create a new attribute of the appropriate length and update your integration, mapping the new target attribute to the source attribute
3. Apply a left expression in the integration. In this example, I am asking for the first 50 characters of the source field; left([SourceField],50)

The given key was not present in the dictionary.

• This happens when a dimension's name has been altered and has not been updated in the integration's Task Mapping.
• In BC, this can happen when you have unposted transactions with the company.

• Remap the dimension in the task manager
• Post your transactions

• In QS, you can redo the wizard to see if it resolves the issue,
• You can reselect the source object in the task details.
• You can redo the integration.

1. Use a different field from your source that has unique values. Solver does not use a DISTINCT function. Examples of unique fields are typically ID fields on the source side.

2. In your target field, code, you can use INCREMENT()/2 in the expression builder and set your description field to the ID you want to use in a staging dimension. You will then need to run a rule to update the module. If you need assistance with this. Please contact Solver Support.

1. Ensure there are no dimension set ids that are all 0s

2. Ensure that no Dimension codes were renamed as now Solver is calling on a code that does not exist

3. Contact your BC consultant for further issues and work with Solver.