Friday, November 10, 2017

Free Precipio SFTP file transfer and data export tool - New Version 1.41 released

By Steve Endow

I have released a new version of my free SFTP file transfer and data export tool for Dynamics GP.

The new version 1.41 can be downloaded from my web site:


Version 1.41 includes the following enhancements:

  • Add support for optional SQLTimeout setting in config file to increase SQL command timeout
  • Set default SQLTimeout to 60 seconds if setting is not present in config file
  • Increase SFTP Connection Timeout from 5 seconds to 30 seconds, and Idle Timeout from 10 seconds to 30 seconds

The SQL Timeout setting allows for longer running queries, or queries that result in larger export files. 

The SFTP Connection Timeout was increased to accommodate some SFTP servers that might not complete the connection process in 5 seconds.

If you use the SFTP application, please let me know! I'd love to hear how you are using it and if it is working well for you.

You can also find him on Twitter, YouTube, and Google+

Dynamics GP BlackLine Integration Upload
Dynamics GP Coupa Integration Upload
Dynamics GP IQ BackOffice Integration Upload
Dynamics GP SFTP Integration Upload
Dynamics GP SFTP File Transfer Upload

Wednesday, November 1, 2017

Beware of UTC time zone on dates when importing data into Dynamics GP!

By Steve Endow

Prior to this year, I rarely had to deal with time zones when developing integrations for Dynamics GP.

The customer was typically using GP in a US time zone, the SQL Server was on premise in that time zone, and all of their data usually related to that same time zone.  Nice and simple.

Dynamics GP then introduced the DEX_ROW_TS field to several tables, and I would regularly forget that field used a UTC timestamp.  That was relatively minor and easy to work around.

But with the increasing popularity of Software As A Service (SaaS) platforms, I'm seeing more and more data that includes UTC timestamps.  I didn't think too much about this until today, when I found an issue with how a SaaS platform provided transaction dates in their export files.

Here is a sample data from a file that contains AP Invoices:


This is a typical date time value, provided in what I generically call "Zulu time" format.  Apparently this format is defined in ISO 8601.

The format includes date and time, separated by the letter T, with a Z at the end, indicating that the time is based on the UTC time zone.

So why do we care?

Until today, I didn't think much of it, as my C# .NET code converts the full date time string to a DateTime value based on the local time zone, something like this:

string docDate = header["invoice-date"].ToString().Trim();
DateTime invoiceDate;
success = DateTime.TryParse(docDate, out invoiceDate);
if (!success)
        Log.Write("Failed to parse date for invoice " + docNumber + ": " + docDate, true);

This seemed to work fine.

But after a few weeks of using this integration, the customer noticed that a few invoices appeared to have the incorrect date.  So an 8/1/2017 invoice would be dated 7/31/2017.  Weird.

Looking at the data this morning, I noticed this in the SaaS data file for the Invoice Date field:


Do you see the problem?

The SaaS vendor is taking the invoice date that the user in Colorado enters, and is simply appending "T06:00:00Z" to the end of all of the invoice dates.

Why is that a problem?

Well, when a user in Colorado enters an invoice dated 8/25/2017, they want the invoice date to be 8/25/2017 (UTC-7 time zone).  When the SaaS vendor adds an arbitrary time stamp of 6am UTC time, my GP integration will dutifully convert that date into 8/24/2017 11pm Colorado time.

For invoices dated 8/25, that may not matter too much, but if the invoice is dated 9/1/2017, the date will get converted to 8/31/2017 and post to the wrong fiscal period.

To make things even more fun, I found that the SaaS vendor is also storing other dates in local time.


So I have to be careful about which dates I convert from UTC to local time, and which ones I truncate the time to just get the date, and which ones are local time.  In theory, the .NET date parsing should handle the conversion properly, assuming the time zone is correct, but I now know that I have to keep an eye on the vendor data.

I will be contacting the vendor to have them fix the issue with the invoice dates--there is no good reason why they should be appending "T06:00:00Z" to dates.

Expect to see a lot more of this date format and related date issues as more customers adopt cloud-based solutions and services.

You can also find him on Twitter, YouTube, and Google+