Today's #TipTuesday is about one of the many things this error message could relate to, and it's in the context of Dynamics GP and SmartConnect/eConnect integrations (not development, where it can also occur regularly for many other reasons). This error came up when I was attempting to preview my data in the Data Source section of the SmartConnect map and I couldn't move past it until I figured out the cause.

Background

The context around this post is I've been creating a new integration in SmartConnect for financial transaction imports from another internal system. I'm making some changes to that process since it was originally designed that every unique transaction type (billing, payments, adjustments etc.) would be its own SmartConnect map when in reality we only need 2 - one for GL transactions and one for Accounts Payable transactions. There are 14 maps today. 😄

Step 1 was creating a test map using the same source data the current maps use. The goal was to achieve the same result as the current integration: 1 journal entry per source journal but batch transactions by type whereas today every single journal entry imported is in its batch.

Step 2 was refining the source data to further improve the process. The source data doesn't have any date columns for instance so the maps would integrate to Dynamics GP with the system date as the transaction date. For financial transactions, that's not good enough if there is a chance the integrations might not be run on the day the source data is created. Any time the maps have failed, by the time they are pushed through again, it's the next business day (or more, in the case of holidays and weekends) so the users would be manually editing transaction dates to get them correct. That shouldn't be needed.

TIL: I'm a creature of habit

Fast forward to the good part. "TIL" (Things I've learned) are that I'm a creature of habit, without really realizing it. I use the same abbreviations and naming conventions for things all the time and this is where the error began.

On my first map, where I was just playing with getting a proper grouping for batches and transactions, I created a calculated column for the date and called it TrxDate. Since there was no source date column, I had to create one. No big deal, it was easy to do and the map worked, and I forgot all about it.

Now in my refined source data, I was creating a staging table to house the data to be integrated including a proper Date column for the transaction date. Guess what I called the field in that table? That's right, I also called it "TrxDate". By the time I was working on this part, I had completely forgotten about the calculated fields. Out of sight, out of mind, I guess!

Troubleshooting this error

There can be a few reasons for this error. I was convinced the problem was authentication-related, and that I didn't have access to the data. I looked up, down and sideways at the SQL permissions for the database I was reading from and I was going a little crazy. (haha) I got stuck in a loop checking permissions on my user account, on the SmartConnect account, and on the eConnect service account. I was convinced it was one of the three but everything looked fine. (PS, it would be MY permissions that were important here, not SmartConnect or the service account).

Next, I changed the ODBC connection to point to another database on the same source server. That connection didn't work either but the error was meaningful, basically telling me "XYZ table does not exist in the ABC database". That made sense. That told me my connection was good, in terms of permissions. So what was possibly going wrong?

I copied and pasted my error into SQL running the query as myself to ensure it still wasn't permissions and it wasn't.

I read the eOne Solutions blog about this error and it talked about duplicate column names (at the time) I was thinking "I don't have duplicate column names in my source query", so I moved on without paying much attention to it. Spoiler alert: I should have!

I would love to say the light bulb went off when I read that blog post but it didn't, because I didn't have local or global constants, or duplicate column names in my staging table. I missed the important part: "or any additional column name". I stumbled across it ultimately by changing my source query to pull fewer columns. Initially, I had defaulted to "select * from XYZ" just to get on with testing and the new column name TrxDate conflicted with a calculated column called Trx Date! Once I selected fewer columns, it returned a data preview without error and that's when I realized it was a column name issue somehow. Go figure.

Conclusion

For anyone else running into this error and it's not permissions-related, try simplifying the query to just pull fewer columns of data. By simplifying I mean (if it's a select statement for instance), select one or two columns temporarily so that it will successfully preview the data. Then add back columns to the query until at some point the one causing the issue will be found. At that point look to see if there are calculated fields, global constants etc. with that field name that are causing the issue. When using an Excel source, try changing the column names one at a time until users can successfully preview the data, to know which column name is conflicting with something else.

What I don't understand entirely is why "previewing" source data (which is where I got the error) would even be looking at calculated fields at that point. That's a SmartConnect issue/question though, which I will ask them about. In my way of thinking, at that point, it should be a simple query result returned regardless of what calculated fields I have because I'm only trying to validate the connection. I can see a "checkpoint" occurring if I were to open a mapping window where it can then check source columns vs. global constants, and additional columns inside of the mapping and tell me there is an issue then.

My understanding of this exact error message is it's an unhandled exception, there isn't an explicit check on clicking the Preview button that looks for this but the code is finding a duplication and returning this error as a result. It seems like it *could* be handled so that it returns a "duplicate column names found" type of message instead, which would be far more useful for the end user.