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.
-
Click the New button.
-
Enter a Store Name: for the ODBC store. ShipStation will create and use this store to connect to your ODBC source.
The store name is shown in the ODBC application and in ShipStation.
-
Select the ODBC data source you are connecting to ShipStation from the drop-down.
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.
-
If required, enter the username: and password: used to connect to your data source.
Not all data sources require a username and password.
-
Click the Test Connection button to ensure that you can successfully connect to the selected ODBC data source.
-
Click the Next button.
You will be taken to the Import Settings screen.
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:
-
Table: If you will be importing your data from a single table, select Table and see the Import From a Single Table section below.
-
Query: If your data will be imported from multiple tables or if you need to write a custom query, select Query and see the Import From a Custom Query section below.
When importing data from a single database table:
-
Select Table for how data will import into ShipStation.
-
Click the Table Source drop-down menu and select the table in your database from which the data will be imported. Then, click Next.
-
Follow the instructions in the Import Field Mappings section of this article to continue your ODBC setup.
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.
-
Select Query as the Column Source:. Then, click Next.
-
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.
-
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.
-
-
Click the Next button once your query is executed successfully.
-
Follow the instructions in the Import Field Mappings section of this article to continue your ODBC setup.
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.
-
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:
-
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.
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.
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.
-
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.
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.
On the PostBack Options screen, select how you would like ShipStation to upload shipment details for your store. You have three available options:
-
Do not upload shipment details: ShipStation will not send shipment-specific information to your data source.
-
Upload shipment details to the same data source: ShipStation will send shipment-specific information back to the same data source you specified in Import Settings.
-
Upload shipment details to a different data source: Select this option if you need to upload your shipment details into a separate data source from the source you selected for import.
-
Select the Upload shipments details to the same data source option. Then, click Next.
-
Select the Column Source you will use to upload shipment details. There are two options:
-
Table: Select Table if all of your shipment data will be exported into a single table. Then, select the table to which shipment details will be exported from the Table Source drop-down menu. Click Next to begin postback mapping.
View the Postback Field Mapping section below for more information on mapping the postback fields.
-
Query: Select Query if your data will be exported into multiple tables or if you need to write a custom query. Click Next to enter your query in order to specify how and to where your shipment details will be imported.
View the Custom Postback Query section below for more information.
-
-
Select the Upload shipment details to a different data source option. Then, click Next.
-
Select the ODBC data source from the drop-down.
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.
-
If required, enter the username: and password: used to connect to your data source.
Not all data sources require a username and password.
-
Click the Test Connection button to ensure that you can successfully connect to the selected ODBC data source.
-
Click the Next button.
-
Select the Column Source you will use to upload shipment details. There are two options:
-
Table: Select Table if all of your shipment data will be exported into a single table. Then, select the table to which shipment details will be exported from the Table Source drop-down menu. Click Next to begin postback mapping.
View the Postback Field Mapping section below for more information on mapping the postback fields.
-
Query: Select Query if your data will be exported into multiple tables or if you need to write a custom query. Click Next to enter your query in order to specify how and to where your shipment details will be imported.
View the Custom Postback Query section below for more information.
-
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.
-
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 #.
-
Click the Finish button.
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.
-
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.
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.
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.
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.
-
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.
-
Tokens are enclosed in {{ }}
-
Tokens are automatically quoted when your query is executed. Do not include quotes around token values.
-
-
Click the Finish button.
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.
-
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.
-
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.