If your business uses an e-commerce solution or ERP that records customer shipping information, then chances are you can integrate your shipping database with the UPS shipping platform, WorldShip®.
Simply put, you can ship packages by keying in order numbers or any unique reference number. All relevant fields can be customized to automatically populate from a database. WorldShip even supports foreign key relationships, which is pretty awesome.
This post will cover MySQL, but the basic concepts apply to all databases with ODBC support.
Step 1: Downloading ODBC Drivers
ODBC, or Open Database Connectivity, is a software standard that allows applications like WorldShip to interact third party databases.
First you will need to install the ODBC drivers for the type of database you are connecting to. Most major database vendors will provide an ODBC driver for free. If you’re using a proprietary database, then you will need to contact your software vendor for driver access. Some vendors will charge you for the driver package.
Note: You will need the drivers for the back-end database your software uses, not the front end software itself. For example, this website runs on WordPress, but the back-end database is MySQL. Here are some common ODBC drivers.
The MySQL drivers Download page
The MariaDB drivers Download page
The Microsoft Access drivers Download page
32-bit / 64-bit Drivers: This is not the architecture for your version of Windows. Windows will allow you to install either the 32-bit or 64-bit driver according to your client, which is WorldShip in this case. Download the version that matches your UPS WorldShip architecture. In my experience, this is 32-bit on most WorldShip installations.
MariaDB / MySQL: These two database softwares are meant to be compatible, so either connection driver will work with either one.
If you have neither MySQL nor Microsoft Access databases, or if you don’t have your ODBC driver installed yet, you can still follow along by downloading my example CSV/XLSX file. Windows has a built in driver for CSV or XLSX files.
Examples: Shipping Database.xlsx / Shipping Database.csv
Step 2: Adding an ODBC Data Source
Run (WinKey + R) the command ‘control admintools’ to launch Windows Administrative Tools, or go to the Control Panel and click on ‘ Administrative Tools’, and the click on ODBC Data Sources (32-bit).
You will need to choose the correct 32/64-bit module depending on your WorldShip architecture. I will be using the 32-bit version.
From the ODBC screen, you can choose to use either the ‘User DSN’ option or the ‘System DSN’ option from the tabs. The difference is the ‘User DSN’ is available to the current user, whereas the ‘System DSN’ is operating-system wide. Both 32-bit and 64-bit data sources will be listed, but you have to use the correct 32-bit or 64-bit module to modify the data sources.
Click ‘Add’ to add a new Data Source. At this point, you will need the credentials for your MySQL database, as well as the specific database you are accessing.
Choose the appropriate driver for your database. I will use the MySQL ANSI driver.
You will see a dialog box for your MySQL database information. This can all be changed later on. Once you’ve entered in your MySQL server address along with valid credentials, you will be able to select from a list of databases from the drop down box. My example database here happens to be called ‘orders.’
Step 3: Connecting With UPS WorldShip
From WorldShip, click on the ‘Import-Export’ tab and then Create a new Map.
You will need to create a new map. Select ‘Import from ODBC Database’ with the Map Type of ‘Shipment.’ You can name it whatever you please, and click ‘Create.’
You will then choose the ODBC source you created earlier, and click ‘OK.’ You will likely have to re-enter your Database credentials at some point.
Once the connection is established, you will need to map your database table columns on the left to the UPS WorldShip fields on the right. Most fields in UPS WorldShip can be mapped from a database connection, but this simple example only has basic shipping information. Some fields are required for shipment, but they don’t have to be mapped here. For example, the package weight is required for shipping, but it can be blank here and entered at the time of shipment.
Important: One database column on the left has to be defined as the primary key for import even if the field itself isn’t mapped. In this case, ‘ordernumber’ is my primary key, but I haven’t mapped it to a field in UPS WorldShip.
Foreign key relationships can also be defined at this point.
Once your database is connected and your fields mapped, you will need to select ‘Keyed Import’ and from the drop down, select the name of your Import Map.
You will see a new persistant dialog box where you can key in order numbers and WorldShip will now read from your MySQL database.
If you have an electronic scale connected to WorldShip, you can really just enter in an order number and hit ship.
Important notes:
- If your database is ever disconnected you may have to re-select your database by going to Import/Export > Keyed Import and then choosing your database.
- If your database keeps order information in one table and the actual shipping address in another table, you will have to setup a foreign key relationship. Both tables should have one common field that is used to create this relationship (an arbitrary “ship to” ID number, for example).
- Windows has two different “ODBC Data Sources” managers in the Control Panel. Although all data sources will be listed in either one, YOU MUST OPEN THE CORRECT DIALOG FOR YOUR ARCHITECTURE. The 32-bit ODBC will allow you to modify 32-bit sources and the 64-bit ODBC will allow you to modify 64-bit sources.
- Some databases, such as MySQL, require explicit permissions in order to be accessed either locally or remotely. A UPS Worldship computer on your local network can access the MySQL database served by your website, but relevant usernames, passwords, and hosts will have to be defined within MySQL. Other databases may not have application-level security at all and instead might rely on host or network level security.