ShipStation ODBC – ShipStation

ShipStation ODBC is an advanced tool that connects ShipStation to an existing ODBC database. If you are not the developer that set up your database, it is advised that you not attempt to make this connection. Please confer with your IT adviser to see if this would be the best fit for your business's needs.

To enable this feature, contact ShipStation Enterprise Support or your ShipStation Account Manager. Your account must be on the Enterprise level subscription plan in order to enable the ODBC option.

Caution

As with any other major configuration, we highly advise backing up all necessary data before proceeding with the connections of your ODBC database to ShipStation.

Install the ShipStation ODBC Client

To connect ShipStation to an ODBC database, first go to your ShipStation account's store settings to download the ShipStation ODBC program. You must install this program on a Windows computer. Detailed instructions below.

Locate the ShipStation ODBC logo on the list and select it. This option will only be available if ODBC has been enabled for your ShipStation account.

Click Download ShipStation ODBC on the page that appears.

Create an ODBC Store

After installing the ODBC client, run the application and log in to the ShipStation ODBC popup using your ShipStation user credentials.

Click New to add a new store.

From here, you'll be prompted to give this new store a name. Enter the name for the store and click Next.

Select the data source that you wish to connect.

If the data source requires authorization to access, this is also where you will enter the username and password for authentication.

If you've already created the data source for a previous connection, you'll be able to select it as the ODBC Data Source. When you click Next > a new window will open which will allow you to define the Import Field Mappings.

If you haven't set up an ODBC Data Source yet, you'll need to add a new Data Source. The next section will go over creating a new Data Source.

Add a New Data Source

Click Add new Data Source from the data source selection window

Locate the Data Source Name from the User DSN, System DSN, or File DSN tab, depending on the type of data source you're using. Click Add... to create a new data source.

Choose the driver for the data source and click Finish.

odbc_client_addnewdatasource_driver_finish_annotated.png

Choose a Data Source Name and Description (optional) and configure any necessary settings in the Database section. The settings in the Database section will vary based on the driver you chose in the previous step.

Click OK when you're done.

The new data source will be listed under User Data Sources. If you ever need to edit an existing data source, click Configure... to adjust accordingly.

Click OK to be taken back to the step where you choose the ODBC Data Source.

Select the data source you created (and provide authentication credentials, if needed) as the ODBC Data Source, then click Next >.

At this point, a new window will open which will allow you to define the Import Field Mappings.

Edit an ODBC Store

If you need to edit the mappings for an existing ODBC store, locate the ShipStation ODBC icon in the background programs area in the system tray.

Right click the ShipStation ODBC icon and click Settings.

Select the store that needs to be updated and click Edit.

The Edit Store modal will appear next. From here, you can adjust the store name, disable the ODBC store, adjust the Data Source and Mappings for Data Import and Postback, and set up Custom Queries.

Clicking the link next to Mapping for Data Import and Data Postback will let you redefine the Import Field Mappings and Postback Data Mapping. When editing, the modal will look the same as when you set up the mappings.

Click Save once you're done making changes to the mappings.

Click Done when you're finished editing the store

Import Field Mappings

When creating or editing an ODBC store connection, you'll be able to set the Import Field Mappings, which is how data will be translated from the order source into ShipStation.

First, select the data source at the top of the Import Field Mapping modal.

There are many available fields to import, but only a few are actually required. The required fields are:

Order #
Last Modified Date (or Order Date)
Full Name (or First Name and Last Name)
Address 1
City
Zip/Postal Code
Country (2-character ISO 3116-1 country code.)

Note

Keep in mind that you must pass the Last Modified Date (or Order Date) value to in the same timezone that is shown on the machine your database is on.

The date-time format must have values for year, month, day, hour, minute, and second, though they don't have to be in that specific order.

Examples: YYYY/MM/DD HH:MM:SS, MM/DD/YYYY HH:MM:SS, etc.

Orders will not import if either of these criterion are not properly met.

The Auto Match feature maps each Data Source to their corresponding ShipStation field. The matching is based off of the Field Name.

Additionally, you can map these fields manually if you prefer, or if Auto Match doesn't sync properly.

First, set up the order-level fields.

The Order Status field is not required, but highly recommended. If Order Statuses are not mapped, all ODBC orders will import into ShipStation as Awaiting Shipment.

Once you've entered the Order information, assign the fields for the Items, Shipping Options, Recipient and Billing tabs.

If you're creating a new store, click Next and you'll be directed to set the Postback Data Mapping.

If you're editing an existing store, click Save and you'll be directed back to the Edit Store window so you can finish making changes.

Import Field Mapping Data Table

Below is a list of fields that can be mapped for import with ShipStation ODBC.

Field Name	Data Type	Length	Notes
Order
Order #	string	1…50	Required. ShipStation will use this as both Order Number and Order Import Key.
Order Date	date/time	19	Conditionally Required, if not using Last Modified Date. Must use same timezone as the machine the database is hosted on. Date/time format must include values for year, month, date, hour, minute, and second. Examples: YYYY/MM/DD HH:MM:SS, MM/DD/YYYY HH:MM:SS, etc.
Last Modified Date	date/time	19	Required. Must use same timezone as the machine the database is hosted on. Date/time format must include values for year, month, date, hour, minute, and second. Examples: YYYY/MM/DD HH:MM:SS, MM/DD/YYYY HH:MM:SS, etc.
Ship By Date	date/time	19	Date/time format must include values for year, month, date, hour, minute, and second.
Order Statuses	string	1…50	Click on field to define potential values. Values can be defined as: Awaiting Payment Awaiting Shipment Shipped On Hold Cancelled If this is not defined, all orders will import in the Awaiting Shipment status.
Hold Until Date	date/time	19	Date/time format must include values for year, month, date, hour, minute, and second.
Customer Notes	string	0...1000
Internal Notes	string	0...1000
Gift Indicator	string	1…50	Click on field to define potential values. Values can be defined as: Gift Non-gift
Gift Message	string	0...1000
Order Source	string	0...50
Custom Field 1	string	0...100
Custom Field 2	string	0...100
Custom Field 3	string	0...100
Items
SKU	string	1...50
Name	string	1...200
Quantity	integer	1...99999
Unit Price	decimal	9,2
Unit Shipping	decimal	9,2
Unit Weight	decimal	9,2	Click on field to define weight unit type. Values can be treated as: Ounces Pounds Grams
Image URL	string	0...500
FulFillment SKU	string	0...400
UPC	string	0...100
HS Tariff	string	0…30	Field is only used for international orders.
Country of Origin	string	2	2-character ISO 3116-1 country code. Field is only used for international orders.
Shipping Options
Ship Method	string	0-100	Maps to the Requested Shipping Service in ShipStation.
Insurance Provider	string	0...50	Click on field to define potential values. Values can be defined as: Shipsurance Carrier
Insurance Value	decimal	9,2
Total Weight	decimal	9,2	Click on field to define weight unit type. Values can be treated as: Ounces Pounds Grams
Package Length	decimal	9,2	Click on field to define dimension unit type. Values can be treated as: Centimeters Inches
Package Width	decimal	9,2	Click on field to define dimension unit type. Values can be treated as: Centimeters Inches
Package Height	decimal	9,2	Click on field to define dimension unit type. Values can be treated as: Centimeters Inches
Content Type	string	0...50	Click on field to define potential values. Values can be defined as: Sample Documents Gift Merchandise Returned Goods Field is only used for international orders.
Non-Delivery Options	string	0...50	Click on field to define potential values. Values can be defined as: Return to Sender Treat as Abandoned Field is only used for international orders.
Recipient
Full Name	string	1...100	Required, or use First Name and Last Name.
First Name	string	1...100	Conditionally Required, if not using Full Name
Last Name	string	1...100	Conditionally Required, if not using Full Name
Company	string	0...100
Address 1	string	1…200	Required.
Address 2	string	0…200
Address 3	string	0…200
City	string	1...100	Required.
State	string	2...100	Only U.S. and CA 2-character state codes are supported.
ZIP/Postal Code	string	0...50	Required.
Country	string	2	Required. 2-character ISO 3116-1 country code.
Email	string	0...100
Phone	string	0...50
Residential	string	0…50	Click on field to define potential values. Values can be defined as: Non-residential Residential
Customer Username	string	1...50
Billing
Payment Date	date/time	19	Date/time format must include values for year, month, date, hour, minute, and second.
Total Amount Paid	decimal	9,2
Shipping Amount Paid	decimal	9,2
Tax Amount	decimal	9,2
Payment Method	string	0…200
Full Name	string	1...100
First Name	string	1...100	Can be used instead of Full Name
Last Name	string	1...100	Can be used instead of Full Name
Company	string	0...100
Address 1	string	1…200
Address 2	string	0…200
Address 3	string	0…200
City	string	1...100
State	string	2...100	Only U.S. and CA 2-character state codes are supported.
ZIP/Postal Code	string	0...50
Country	string	2	2-character ISO 3116-1 country code.
Billing Party	string	1…50	Click on field to define potential values. Values can be defined as: My Account Recipient Third Party If you have multiple connections for the same carrier, My Account will use the Primary carrier account.
Billing Account #	string	0…30	Only used when Billing Party is set to Recipient or Third Party.
Billing Zip	string	0…10	Only used when Billing Party is set to Recipient or Third Party.
Billing Country	string	2	Only used when Billing Party is set to Recipient or Third Party.

Postback Data Mapping

When creating or editing an ODBC store connection, you'll be able to set the Postback Data Mappings, which is how ShipStation will send shipment information back to the data source when a shipment or fulfillment is created in ShipStation.

First, choose whether the data source that will be updated is the same as the Import Field Data Source, a different data source, or if you do not want ShipStation to post data back to a data source.

Select the Database Table at the top of the window.

There are fewer fields available when posting back shipment information. The only required field is the Order #.

If you click on the Order # button, you will be able to trim any additional zeroes from the beginning or end of an order number.

Once you've entered the Package information, assign the fields for the Recipient tab. Click Save when you're finished.

If you're creating a new store, clicking Save will complete the process and you'll be taken back to the main window of the ShipStation ODBC Client. You can edit the ODBC Store to make further changes.

If you're editing an existing store, clicking Save will direct back to the Edit Store window so you can finish making changes.

Postback Data Mapping Data Table

Below is a list of fields which can be mapped for postback with ShipStation ODBC.

Field Name	Data Type	Length	Notes
Package
Order #	string	1…50	Required. Click on field to trim order number on post back
Carrier	string	0...50
Service	string	0…80
Package Type	string	0...50
Confirmation Service	string	0...50
Tracking Number	string	0...50
Ship Date	date/time	19	Click on field to define format of post back value
Print Date	date/time	19	Click on field to define format of post back value
Shipping Amount	decimal	9,2
Insurance Provider	string	0...50	Possible values: Shipsurance Carrier
Insured Value	decimal	9,2
Insurance Fee	decimal	9,2
Order Status	string	0…50	Click on field to define post back value. ShipStation will only post back Shipped order status.
Recipient
Full Name	string	1...100
Company	string	0...100
Address 1	string	1…200
Address 2	string	0…200
Address 3	string	0…200
City	string	1...100
State	string	2...100
ZIP / Postal Code	string	0...50
Country	string	2	2-character ISO 3116-1 country code.
Phone	string	0...50

Custom Queries

By default, ShipStation ODBC only imports orders from a single table or file. However, this standard query may not be the best option for your business needs. This can be the case if your data is in multiple tables or you need to specify WHERE or GROUP BY clauses to improve performance or if you need to restrict the data that is being imported. In such cases the better solution may be to create a "View" in your database.

Warning

Any issues caused by custom queries fall beyond our troubleshooting scope. If you decide to use ODBC beyond its default queries, ShipStation will be unable to troubleshoot any issues with order data not properly transmitting or errors this causes on your database.

ShipStation supports the following query types:

BatchOrder	This query is required to import orders.
CountOrders	During import, this query is used to determine how many orders will be loaded into the software. This query is required if using BatchOrder.
PostBack	This query is used to post back status for imported orders. This query is optional if you do not wish to post back the tracking number, status, etc.

If you attempt to import an order using Custom Queries and the required query type does not exist, the import will fail and you will receive an error. To resolve this issue, either disable Custom Queries, or update your query to include the missing query type.

Right click the ShipStation ODBC icon and click Settings.

Select the store that needs to be updated and click Edit.

In the window that appears, check the checkbox for Custom Queries and click Browse to locate the query you wish to customize.

An explorer window will open to a directory containing a readme and sample queries. Your queries must be located in this directory. For more information about the ShipStation custom query variables, import DB column names, and postback values, consult the sample queries and Readme.txt file contained in the directory.

Please note that the following query names are the only available options:

BatchOrder.sql
CountOrders.sql
PostBack.sql

If you ever need to return to the standard queries, edit the store and uncheck the Custom Queries checkbox.

ODBC Checklist

Do you have admin access to your database? Yes No

Can you create new tables or views if needed? Yes No

Who is your database administrator?

Name: ________________________________________
Contact Phone Number: ________________________________________
Contact Email Address: ________________________________________

PLATFORM

Windows Only

Windows XP (32 – bit only)

Windows Vista (32 or 64 bit)

Windows 7 (32 or 64 bit)

Windows 8 (32 or 64 bit)

Windows 10 (32 or 64 bit)

Data Source

Microsoft SQL Server

MS Access

MySQL

PostgreSQL

CSV/TXT delimited files

Other: ________________________________________

Note: We can support any data source that has a support ODBC driver for Windows. If you're unsure if your data source would be supported, please let us know your data source and we'll be happy to review if it is ODBC compatible.

TABLE REQUIREMENTS

All import fields must be within the same table

Note: If this is not possible, you may use custom queries to join tables within the client, however ShipStation cannot help troubleshoot issues from your queries.

REQUIRED IMPORT FIELDS

These fields are required. If they are not included, orders will not import into ShipStation.

Order number

Last Modified Date or Order Date (with timestamp)

Note: Time zone of your database must be matching, or before, the machine’s time zone. This is used for importing orders.

Recipient info

Full Name or First Name and Last Name

Address 1

City

ZIP/Postal Code

Country (2-character ISO code)

OPTIONAL IMPORT FIELDS

These fields are optional, but recommended to make full use of ShipStation's features.

Order items (SKU, name, quantity, etc)

Recipient email

Username (Required to create reusable customer records in ShipStation.)

Order Status

Note: If undefined, the order will go into Awaiting Shipment. If a field is mapped, but a status is not mapped to one of ShipStation’s statuses, the order will not be imported.

POSTBACK REQUIREMENTS

Postback is optional.

Postback only writes to a single data source and a single table.

REQUIRED POSTBACK FIELDS (if postback is desired)

These fields are required. If they are not included, ShipStation will not be able to post information to the table.

Order #

OPTIONAL POSTBACK FIELDS

These fields are optional, but recommended to ensure adequate shipment information is available in the postback table.

Tracking Number

Carrier

Service

ODBC-Checklist.pdf
80 KB Download