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 it’s own SmartConnect map when in reality we only need 2 - one for G/L transactions and one for A/P 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 to batch transactions by type where today every single journal entry imported is in its own 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 actually began.

On my first map, where I was just playing with getting a proper grouping for batches and transactions, I had 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? You guessed 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, 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 own 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 on 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 and (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

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

What I don’t understand entirely is why “previewing” your 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 you have because you’re only trying to validate the connection. I can see a “checkpoint” occurring if you open a mapping window where it can then check source columns vs. global constants, and additional columns inside of the mapping and tell you 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.