ShipStation ODBC

How to set up the ShipStation ODBC client, how to create and edit ODBC stores for order import and postback, and field definitions for ODBC mapping.

ShipStation ODBC is an advanced tool that connects ShipStation to an existing ODBC database. The steps to properly connect merchant databases to ShipStation, fully test the results, and ensure a successful integration all involve an intricate process. 

ShipStation requires the purchase of our ODBC Integration services package to meet the needs of this integration. 

The ODBC integration is now available for all accounts and subscription plans. Complete the ODBC Checklist at the bottom of this page to request ODBC package information and contact your Sales Representative to sign up for the ODBC service package.

Important

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.

  1. 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.

    ODBC tile highlighted.
  2. Click Download ShipStation ODBC on the page that appears.

    ODBC Settings with arrow pointing to the download ShipStation ODBC.

Scope of Support

Installing the ODBC Client to the computer and gaining access to the database used for ODBC are outside of ShipStation's scope of support. We recommend you contact your network administrator or information technology team if you need assistance with installation of the ODBC client or access to the database, as the process can differ based on the unique configurations.

Create an ODBC Store

After installing the ODBC client:

  1. Run the application and log in to the ShipStation ODBC popup using your ShipStation user credentials.

    ODBC Client login
  2. Click New to add a new store.

    ODBC Client Manage stores with New button highlighted.

    From here, you'll be prompted to give this new store a name.

  3. Enter the name for the store and click Next.

    ODBC client New Store Wizard pop-up Store Name field
  4. 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

  1. Click Add new Data Source from the data source selection window

    ODBC Select Existing with Add new data source button highlighted.
  2. 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.

    ODBC client add new data source with Add button highlighted.
  3. Choose the driver for the data source and click Finish.

    Create New Data Source with Finish button highlighted.
  4. 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.

  5. Click OK when you're done.

    ODBC Text Setup menu

    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.

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

    ODBC Client Add new data source with OK button highlighted
  7. Select the data source you created (and provide authentication credentials, if needed) as the ODBC Data Source, then click Next >.

    odbc_client_newstore_datasource.png

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.

  1. Right click the ShipStation ODBC icon and click Settings.

    ODBC client Edit with Settings option selected.
  2. Select the store that needs to be updated and click Edit.

    ODBC client Manage store menu with Edit button highlighted.

    The Edit Store modal will appear next.

  3. Adjust the store name, disable the ODBC store, adjust the Data Source and Mappings for Data Import and Postback, and set up Custom Queries as needed.

    ODBC Client edit store menu
  4. Click the link next to Mapping for Data Import and Data Postback to redefine the Import Field Mappings and Postback Data Mapping, if needed.

    ODBC Client import mapping order tab

    When editing, the modal will look the same as when you set up the mappings.

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

    ODBC client save button
  6. Click Done when you're finished editing the store

    ODBC client edit store with done button highlighted.

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.

ODBC client import field mapping

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.)

Time-Date Format

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.

ODBC Client import mapping order tab

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.

ODBC Client Order Status mapped to Shipped.

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.

  1. 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.

    ODBC client post back data source menu.
  2. Select the Database Table at the top of the window.

    ODBC client Postback Field Mapping pop-up menu

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

    ODBC Client postback mapping Package tab
  3. Click on the Order # button to trim any additional zeroes from the beginning or end of an order number.

    ODBC Client Postback Mapping Order Number
  4. Once you've entered the Package information, assign the fields for the Recipient tab.

  5. 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.

Troubleshooting Custom Queries

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.

  1. Right click the ShipStation ODBC icon and click Settings.

    ODBC client Edit with Settings option selected.
  2. Select the store that needs to be updated and click Edit.

    ODBC client Manage store menu with Edit button highlighted.
  3. Check the checkbox for Custom Queries and click Browse to locate the query you wish to customize.

    ODBC client Edit store with Custom queries option highlighted.

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?   Checkbox Yes   Checkbox No

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

Who is your database administrator?

  • Name: ________________________________________

  • Contact Phone Number: ________________________________________

  • Contact Email Address: ________________________________________

PLATFORM

Windows Only

Checkbox Windows XP (32 – bit only)

Checkbox Windows Vista (32 or 64 bit)

Checkbox Windows 7 (32 or 64 bit)

Checkbox Windows 8 (32 or 64 bit)

Checkbox Windows 10 (32 or 64 bit)

Data Source

Checkbox Microsoft SQL Server

Checkbox MS Access

Checkbox MySQL

Checkbox PostgreSQL

Checkbox CSV/TXT delimited files

Checkbox 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

Checkbox 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.

Checkbox Order number

Checkbox Last Modified Date or Checkbox 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.

CheckboxRecipient info

     Checkbox Full Name or Checkbox First Name and Last Name

     Checkbox Address 1

     Checkbox City

     Checkbox ZIP/Postal Code

     Checkbox Country (2-character ISO code)

OPTIONAL IMPORT FIELDS

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

Checkbox Order items (SKU, name, quantity, etc)

Checkbox Recipient email

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

Checkbox 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.

Checkbox 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.

Checkbox Order #

OPTIONAL POSTBACK FIELDS

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

Checkbox Tracking Number

Checkbox Carrier

Checkbox Service