ShipStation ODBC: Add a Store & Connect a Data Source

Once you have installed the ShipStation ODBC client software and logged in, the next step is to add an ODBC store to ShipStation. Then, you will connect and configure your data source.

Scope of Support

Installing the Windows ODBC Drivers accessing the database used for ODBC are outside of ShipStation’s scope of support. We recommend that you contact your network administrator or information technology team if you need assistance with the installation of the ODBC drivers or with accessing the database.

Create an ODBC Store

  1. Click the New button.

    Click the New button to connect a new ODBC store.
  2. Enter a Store Name: for the ODBC store. ShipStation will create and use this store to connect to your ODBC source.

    A store name has been entered into the Store Name: field.

    The store name is shown in the ODBC application and in ShipStation.

  3. Select the ODBC data source you are connecting to ShipStation from the drop-down.

    The select the ODBC data source drop-down is expanded and a data source is selected in the list.

    Manage Data Sources

    If the data source to which you are attempting to connect is not shown in the list, you can click the Manage Data Sources button to open the Windows ODBC Data Source Administrator dialog from which you can add a custom data source. Consult with your system administrator for information on how to add your data source.

    The manage data sources button is highlighted.
  4. If required, enter the username: and password: used to connect to your data source.

    Not all data sources require a username and password.

    STORE_ODBC_UserNamePassword_MRK.png
  5. Click the Test Connection button to ensure that you can successfully connect to the selected ODBC data source.

    STORE_ODBC_BTN_TestConnection.png
    STORE_ODBC_ConnectionSuccess.png
  6. Click the Next button.

    STORE_ODBC_BTN_Next_MRK.png

    You will be taken to the Import Settings screen.

Configure Import Settings

The Import Settings screen gives you the option to select the Column Source: from which your order information will be imported into ShipStation. You can select from the following two options:

STORE_ODBC_Config_ColumnSource_MRK.png

Import from a Single Table

When importing data from a single database table:

  1. Select Table for how data will import into ShipStation.

  2. Click the Table Source drop-down menu and select the table in your database from which the data will be imported. Then, click Next.

    STORE_ODBC_Config_Table_SEL_Table_MRK.png
    STORE_ODBC_BTN_Next_MRK.png
  3. Follow the instructions in the Import Field Mappings section of this article to continue your ODBC setup.

Import from a Custom Query

The ODBC Query is an available option when setting up an ODBC store in ShipStation. This feature allows you to specify the desired SQL query ShipStation uses to import information from your ODBC database instead of using a single database table.

Advanced Feature

The ODBC custom query is an advanced feature meant to be used by ShipStation users who are familiar with SQL, databases, etc. ShipStation does not validate the queries used and incorrect queries may adversely affect your data. Writing, customizing, or editing custom queries is outside the scope of ShipStation support.

  1. Select Query as the Column Source:. Then, click Next.

    STORE_ODBC_Config_ColumnSource_Query_MRK.png
  2. Enter your query into the Your query for custom import: field.

    Parameterized Query

    The query can be entered as a parameterized query using ? to represent the Last Modified Date.

    During the initial order import ? will represent a Last Modified Date of 30 days prior to the date of the initial import.

    On subsequent order imports ? will represent the most recent Last Modified Date of all imported orders.

    STORE_ODBC_Config_Query_MRK.png
  3. Test your query by clicking the Run Query button.

    • Entering a date into the Last Modified Date Value: field will replace the Last Modified Date parameter (?) when testing the query.

    STORE_ODBC_Config_BTN_RunQuery_MRK.png
  4. Click the Next button once your query is executed successfully.

  5. Follow the instructions in the Import Field Mappings section of this article to continue your ODBC setup.

Import Field Mappings

The Import Field Mappings screen gives you the ability to map the data from your data source to the available fields in ShipStation. Data fields to be mapped are broken down into the following types:

  • Order: Order-specific data such as Order#, Order Date, Last Modified Date, etc.

  • Items: Item data specific to the items on the orders such as SKU, Item Name, Quantity, Price, etc. The Items tab also allows you to specify if your items are on a single row or on multiple rows in your database.

  • Shipping Options: Data specific to the shipment such as Ship Method, Weight, Dimensions, etc.

  • Recipient: Data specific to the recipient of the order such as Name, Address, Phone number, etc.

  • Billing: Data specific to the billing of the order such as Payment Date, Amount Paid, Tax, Shipping Charges, etc.

See the Import Field Mapping Data table in ShipStation's ODBC: Data Mapping article for detailed information on the available fields in ShipStation.

  1. Map the fields in your data source to the available fields in ShipStation by selecting the field in your data source from the available drop-down menus.

    We recommend mapping the fields on the Order tab first, then continue mapping the Items, Shipping Options, Recipient, and Billing tabs.

    See the Things to Know section below for additional tips and information related to field mapping.

    Required Fields

    Please note that any field indicated with (required) requires mapping. There are many available fields to import, but only a few are actually required. The required fields are:

    • Order > Order #

    • Order > Order Date

    • Order > Last Modified Date

    • Order > Order Status

    • Item > Name

    • Item > Quantity

    • Item > SKU

    • Recipient > Full Name or First Name and Last Name

    • Recipient > Address 1

    • Recipient > City

    • Recipient > ZIP/Postal Code

  2. Click the Next button once you have completed the field mapping. You will be taken to the Postback Options screen to select how you wish for shipment data to be posted back to your data source.

STORE_ODBC_Config_ImportMap.png

Things to Know

Below are a few helpful things to know about mapping your fields to the available fields in ShipStation.

Time-Date Format

Keep in mind that you must pass the Last Modified Date (or Order Date) value 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 criteria is not properly met.

Auto Match

The Auto Match feature maps each Data Source to their corresponding ShipStation field. The matching is based on the Field Name. Additionally, you can map these fields manually if you prefer, or if Auto Match doesn't map the fields properly.

STORE_ODBC_Config_BTN_AutoMatch_MRK.png
Items Mapping

When mapping item fields on the Items tab you must select how line items are organized in your data source. Select from the following options:

  • Separate row per item: Select this option if the line items on an order are listed on separate rows in your data source. Then, map the fields in your data source to the available ShipStation fields.

    STORE_ODBC_SeparateLineItems_MRK.png
  • All items on a single row: Select this option if the line items are listed on a single row in your data source. Then, use the Add Item button to add the maximum number of line items available for import. Be sure to map the fields for each added item.

    STORE_ODBC_Config_ItemsSameRow_MRK.png
Specific Field Mapping and Unit Selection

Some fields, such as the Order Status field on the Order tab, require the field values in your database to be mapped to the available field values in ShipStation. Other fields, such as the Unit Weight field on the Items tab require that the unit of measure being imported into ShipStation be selected.

See the Fields Requiring Additional Mapping section of ShipStation's ODBC: Field Mapping article for detailed information.

STORE_ODBC_Config_OrderStatusMapping_MRK.png
STORE_ODBC_Config_WeightMapping_MRK.png

Configure Postback Options

On the PostBack Options screen, select how you would like ShipStation to upload shipment details for your store. You have three available options:

Upload Shipment Details to the Same Data Source

  1. Select the Upload shipments details to the same data source option. Then, click Next.

    Postback Settings screen is displayed with the option to post back to the same data source selected.

Upload Shipment Details to a Different Data Source

  1. Select the Upload shipment details to a different data source option. Then, click Next.

    STORE_ODBC_Config_Postback_DifSource.png
  2. Select the ODBC data source from the drop-down.

    STORE_ODBC_Config_PostBack_SEL_DataSource_MRK.png

    Manage Data Sources

    If the data source to which you are attempting to connect is not shown in the list, you can click the Manage Data Sources button to open the Windows ODBC Data Source Administrator dialog from which you can add a custom data source. Consult with your system administrator for information on how to add your data source.

    The manage data sources button is highlighted.
  3. If required, enter the username: and password: used to connect to your data source.

    Not all data sources require a username and password.

    STORE_ODBC_Config_Postback_UsernamePassword_MRK.png
  4. Click the Test Connection button to ensure that you can successfully connect to the selected ODBC data source.

    STORE_ODBC_BTN_TestConnection.png
    STORE_ODBC_ConnectionSuccess.png
  5. Click the Next button.

Postback Field Mapping

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

Data fields to be mapped are broken down into the following types:

  • Package: Shipment-specific data related to the order such as the ship date, tracking number, carrier, service used, etc.

  • Recipient: Data specific to the recipient of the order such as Name, Address, Phone number, etc.

See the Postback Mapping Data Table section of ShipStation's ODBC: Data Mapping article for detailed information on the available postback fields.

  1. Map the fields in your data source to the available fields in ShipStation by selecting the field in your data source from the available drop-down menus.

    Be sure to map the fields on both the Package and Recipient tabs.

    See the Things to Know section below for additional tips and details on mapping the postback data fields.

    Require Fields

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

  2. Click the Finish button.

    ODBC_BTN_Finish.png

    Edit Store Import and Postback Settings

    After clicking the Finish button you are taken to the main ODBC screen and your new store is shown in My Stores. From this screen, you can activate or deactivate the store and edit the import and postback settings. See the ShipStation ODBC: Edit a Store article for detailed information.

    STORE_ODBC_Config_EditStore_MRK.png
  3. Click Done. Then, click the Done button to close ShipStation ODBC.

    ShipStation ODBC is still running and can be accessed from the Windows system tray.

Things to Know

Auto Match

The Auto Match feature maps each Data Source to their corresponding ShipStation field. The matching is based on the Field Name. Additionally, you can map these fields manually if you prefer, or if Auto Match doesn't map the fields properly.

STORE_ODBC_Config_BTN_AutoMatch_MRK.png
Specific Field Mapping and Unit Selection

Some fields, such as the Insurance Provider field on the Package tab, allow for the field values in your database to be mapped to the available field values in ShipStation. Other fields, such as the Set Date Format allow for selecting the date format being imported into ShipStation.

STORE_ODBC_Config_Postback_SpecificMapping_MRK.png

Custom Postback Query

The ODBC Query is an available option when setting up an ODBC store in ShipStation. This feature allows you to specify your desired SQL query ShipStation utilizes to upload shipment information to your ODBC database instead of using a single database table.

Advanced Feature

The ODBC custom query is an advanced feature meant to be used by ShipStation users who are familiar with SQL, databases, etc. ShipStation does not validate the queries used and incorrect queries may adversely affect your data. Writing, customizing, or editing custom queries is outside the scope of ShipStation support.

  1. Enter your query into the Your query for custom postback: field.

    Things to Know

    A sample query highlighting a few of the tokens that can be used for posting shipment details to your database is provided for you in the Your query for custom postback: field.

    STORE_ODBC_Config_Postback_CustomQuery.png
  2. Click the Finish button.

    ODBC_BTN_Finish.png

    Edit Store Import and Postback Settings

    After clicking the Finish button you are taken to the main ODBC screen and your new store is shown in My Stores. From this screen, you can activate or deactivate the store and edit the import and postback settings. See the ShipStation ODBC: Edit a Store article for detailed information.

    STORE_ODBC_Config_EditStore_MRK.png
  3. Click Done. Then, click the Done button to close ShipStation ODBC.

    ShipStation ODBC is still running and can be accessed from the Windows system tray.

Additional Reading

  • ShipStation ODBC: Field Mapping: Learn more about the available fields when configuring import and postback mapping, available postback tokens, and information on additional field mappings.

  • ShipStation ODBC: Edit a Store: Learn more about activating or deactivating an ODBC store with ShipStation and how to edit your import and postback field mappings.