pasPortal: pasTransfer 4.25.2.0 RTM

pasTransfer 4.25.2.0 RTM

Written By Gary Fletcher

pasTransfer 4.25.2.0 RTM is now generally available.

This release of pasTransfer contains updates to help customers negatively affected by a recent Windows update by Microsoft that impairs the use of the Microsoft JET OLEDB provider to process Excel spreadsheet files.

Basically what happened is this: on October 10th, 2017 Microsoft issued security releases for all currently supported version of Windows that *fixed* an undisclosed security issue related to their search services. This update was released without adequate testing and consequently any and all applications that use the Microsoft JET OLEDB provider to process data will receive the error message “Unexpected error from external database driver (1). (Microsoft JET Database Engine)" when they attempt to read data. This is documented here. Initially Microsoft acknowledged the issue and promised a resolution in an upcoming patch. They have since backpedalled and decided that the JET provider will not be fixed and that developers need to migrate to the newer Microsoft ACE OLEDB Provider.

The problem is two-fold. First, pasTransfer historically relied on the JET provider for processing all files with the .XLS extension so that it could read legacy file formats and used the ACE provider for all .XLSX, .XLSM, and .XLSB files. Neither provider is included by default on modern operating systems nor do they ship with pasTransfer and need to be downloaded from Microsoft and installed on a Windows computer. Second, the ACE provider does not read all the various file formats that the JET provider did despite what Microsoft documentation claims. Many old Excel file formats like Excel 3.0 and Excel 5.0 cannot be read through the ACE provider.

To attempt to address the problems this security patch has created for our customers we have released an updated version of pasTransfer with new behavior for processing Excel files and some general guidance. The new behavior is that all connections that read data from Excel that were previously coded to use either JET or ACE providers based on file extensions will now instead default to using the most recent version of the ACE provider if it is installed and failing that will fallback to using the most recent JET provider if it is installed (and not impaired by the security patch) and failing that will not be able to process the file without some additional work (detailed below). This behavior should satisfy the vast majority of customers with no configuration changes to their existing integrations as long as they do not have Excel files in formats prior to Excel 97.

Your first question is probably: who has Excel files in a format 20+ years old. Answer: a lot of people - maybe even you. Your second questions is probably: why would anyone purposely generate Excel files in a 20+ year old format? Answer: because it was a least common denominator format that several legacy platforms could process and as a result there are still modern applications today that do that. By crippling the JET provider Microsoft just raised the least common denominator and a lot of people are going to be affected.

For those few customers that still have very old Excel files in 3.0 or 5.0 format that cannot be processed by ACE and will not be processed by JET because the security patch is installed that impairs it we can offer some workarounds and guidance. First, see if the software generating the files has an option to use a newer format - problem solved. Second, follow the workaround documented here as Approach 3 to re-enable the JET provider on patches machines - problem solved. Third, if you have the pasUnity automation engine installed you can use a Managed Code job step to automate a locally installed copy of Excel to open the legacy file and save it in a new format before processing it in pasTransfer. If this is you and you have a valid support contract contact us we will provide you with a sample Job Step definition you can import into pasUnity automation and customize for your own purposes at no cost.

Back to pasTransfer now. So with these workarounds and such listed above why did you release a new pasTransfer? Well first we were using the JET provider for .XLS files and with that impaired for most of our customers we really did need to release an update that would change to the ACE provider. On top of that though we also decided that if there were multiple version of the JET or ACE providers on your system we would give you the option of choosing which provider you wanted to use to process files.

By default (and in almost all scenarios) you can get by using the default behavior of allowing pasTransfer to autosense and select the most suitable OLEDB provider as shown below:

From the Excel Provider Override drop down list you can select a specific provider if you do not want to use the default. By the way: they are listed in order of precedence so the top one is what is going to be used if Automatic is selected.

To see if your provider choice is going to work you can press the refresh button and then click the Data Region name drop down. If the list is empty your provider did not work. If the list has at least one entry then it worked fine (as shown in the example below).

Additionally, you may be wondering where you go to get the latest version of the Microsoft JET and ACE OLEDB providers so let me point you in the right direction:

We remain ever vigilant and will continue to monitor this and several other issues to ensure that we have solutions either in advance of or as soon as possible after a situation that impacts customers arises.