SFMC Automation Error: Automation failed due to system error.

Recently, I encountered an automation that repeatedly failed during a Query activity, with the dreaded error message: “Automation failed due to system error.” Despite my best efforts to modify the query and explore other avenues, I couldn’t resolve the issue.

Naturally, I turned to Google for answers. The majority of responses pointed to common culprits: 

• Primary Key Violation: If your query generates duplicate rows that violate the primary key constraints, it can lead to failure. 
• Inserting Null Values: Attempting to insert null values into non-nullable fields can trigger errors.
• Data Truncation: Inserting values longer than the field allows (resulting in truncation) can cause issues.
• Timeouts: If your query exceeds the 30-minute timeout window, it will fail. 

Despite checking my query against these possibilities, none seemed to fit my case. 

Desperate for a solution, I contacted Salesforce support. Their investigation uncovered a critical detail: an ‘Invalid length parameter passed to the LEFT or SUBSTRING function.’ This error originated from a formula I had created using the SubString and CharIndex functions. Regrettably, the result of my CharIndex function was a negative value, which then caused an erroneous input for the SUBSTRING function.

The lesson learned? When encountering the dreaded “Automation failed due to system error.” message, consider these factors: 

Feasibility of Actions: Ensure that any action you take is technically feasible. Formula functions should receive valid input values. 

Field Compatibility: Verify that the field supports the data type you’re trying to insert. 

If all else fails, don’t hesitate to seek Salesforce support. They can delve into the backend, identify the root cause, and guide you toward a resolution.