The original Rate & Boundary Import was done using native Dexterity code and local c-tree temporary tables. This import could take a substantial amount of time particularly when using the Zip+4 Option. This prompted us to create the SQL Optimized Import to minimize the time required for this process.

The SQL Optimized Import¹⁾ starts out by using the Transact SQL BULK INSERT command to quickly read some of the larger files included in the Rate & Boundary Database. In order for BULK INSERT to function properly:

• The Rate & Boundary Database must be located on a local drive on the SQL Server machine.
• The Path on Server must be properly set in the DZ Rate And Boundary Import window.
• The user running the import must be assigned the bulkadmin Server Role for the SQL Server. This roll must be assigned to the Microsoft Dynamics GP user's login ID.

IMPORTANT: If BULK INSERT fails, the Rate & Boundary Database files will be read in the traditional (and slower) way. Failure of the BULK INSERT process will not slow down the other import process that have been optimized.

The Non-Optimized Import provides our users with an alternative way to perform the Rate & Boundary Import should the SQL Optimized Import fail for any reason.

The error message indicating that the import file cannot be opened such as:

Cannot bulk load because the file FipsCodes.txt could not be opened. Operating system error code 5(Access is denied.)

seems to typically be related to the security context under which the SQL Server service is running. Switching the SQL Server login to Local System Account may eliminate the error.

Modifications