Secureship provides you with a database integration that allows you to import shipment information into Secureship directly from your Microsoft (MS) SQL database. This integration is intended help you streamline your shipping process and create shipments in little to no time at all.
How the SQL Database Integration works?
By creating a database integration into your system, you will be able to import the address and package information, directly into the ‘create shipment’ form. All you need is to choose the connection name (Store), enter the Order Id, and click ‘Import’. This will save you a lot of time, and ensure there is no mistakes in entering the shipment information.
How to configure the SQL Database Integration to work with Secureship
A connection between Secureship server and your database must be established, then a query has to be saved to allow our server query your database to get the required shipment(s) and package(s) information.
- Login to your Secureship account, and click Connect.
- From the drop-down menu, choose 'Database Connect.'
Note: If this option isn’t available on your account, please email our support team about getting this feature enabled for you.
- Complete the form to establish a connection between Secureship and your database.
- First choose a name for the connection. For example ‘StoreName’, ‘MyStoreDB’, …
- Enter the host name or public IP address of your SQL Server.
- Keep the default port (1433) as it is. Change it if you are using a different port.
- Enter a username and password
- Create and test the query that will be used to pull the information from your system.
- Enter the query that will get all the required data from your database
- Use the column mapping to let the integration understand the structure of your content
Query Column Mapping
In order to pull the information from your database into Secureship, your query must return the data with the following column headers:
Shipment Column Header Names:
FromCompany, FromContact, FromTel, FromAddr1, FromAddr2, FromCountryCode, FromPostalCode, FromCity, FromProvince, ToCompany, ToContact, ToTel, ToAddr1, ToAddr2, ToAddr3, ToCountryCode, ToPostalCode, ToCity, ToProvince
Package Column Header Names:
Weight, WeightUnits, Length, Width, Height, DimUnits, Insurance
WeightUnits:
0 = Lbs, 1= Kgs (other accepted values: lb, lbs, kg, kgs)
DimUnits:
0 = CMs, 1 = Inches (other accepted values: cm, cms, in, inches)
How mapping works:
Mapping your columns to one of the above columns is very simple. All you have to do is return your column with an alias that matches the name of our columns headers, by using the ‘AS’ keyword in the select query.
For example:
The column that holds the company name in your database is called ‘Company’, and the column that have addresses called ‘Address’ and want to map these two columns to our columns ‘ToCompany’, and ‘ToAddr1’.
SELECT Company AS ToCompany, Address AS ToAddr1 FROM MyTable
This query will map column ‘Company’ to our column ‘ToCompany’ and your column ‘Address’ to our column ‘ToAddr1’.
- Once your query is ready and all the columns are mapped, Click ‘Test Query’ to populate the preview tables with your data. Edit the query and re-test it until all the required columns are populated correctly with your data.
- To import one record from your database using on a unique id such as Order No, put {{id}} instead of the required value in the query. Use the {{id}} field for testing.
For Example:
Your query look like this:
SELECT Company AS ToCompany, Address AS ToAddr1 FROM MyTable WHERE OrderNO =12345
Note: You can edit the value ’12345’ to point to the required record.
You should substitute the value of OrderNo with the variable {{id}}. It will become
SELECT Company AS ToCompany, Address AS ToAddr1 FROM MyTable WHERE OrderNO ={{id}}
Note: You can use the separate field {{id}} in the form to enter the required value of OrderNo.
Hint: If the column type is not number, you should place it between quotations. i.e. ‘{{id}}’
When everything look good, ‘Click Save’. Now you can import values from your database. Simply navigate to the Create Shipment page and enter your unique ID to import the data into the form.
Still have questions?
Email our support team and an expert will get in touch with you shortly