SSIS Components for BigCommerce

Build 24.0.9060

Orders

Returns data from Orders table.

Table Specific Information

Select

The component uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

  • Id supports the '=,<,>,<=,>=' comparison.

The rest of the filter is executed client-side within the component.

For example, the following queries are processed server-side: 

SELECT * FROM Orders WHERE Id = 1 

SELECT * FROM Orders where Id > 104

SELECT * FROM Orders where Id < 104

SELECT * FROM Orders where Id >= 104

SELECT * FROM Orders where Id <= 104

Insert

To insert a new Order, at least one order item is required to be attached to the INSERT query for this order. To do so, you can first populate a temporary OrderItems table with the desired items for the order you are about to create, and later using this table as a value for the LinkedProducts Pseudo-Columns during insertion: 

INSERT INTO OrderItems#TEMP (ProductId, QuantityOrdered) VALUES (961, 4);
INSERT INTO OrderItems#TEMP (ProductId, QuantityOrdered) VALUES (962, 3);
INSERT INTO Orders (CustomerId, LinkedProducts, StatusId, ItemsTotal, ItemsShipped, PaymentMethod, OrderIsDigital, BillingFirstName, BillingLastName, BillingCompany, BillingCountry, BillingZip, BillingState, BillingEmail, BillingPhone) VALUES (1, OrderItems#TEMP, 4, 6, 2, 'cash', 'true', 'TSS', 'Dev', 'London Corp.', 'United Kingdom', 23433, 'London', '[email protected]', '+355534445');

Update


UPDATE Orders SET StaffNotes='Testing123' where id=103

Delete


DELETE FROM Orders WHERE Id = 3

Columns

Name Type ReadOnly References Description
Id [KEY] Integer True

The Id of the order.

CustomerId String False

Customers.Id

The Id of the customer assigned to this order.

DateCreated Datetime True

The date of creation for the order.

DateModified Datetime True

The last modification date of the order.

DateShipped Datetime True

The date of shipment for the order.

StatusId String False

OrderStatuses.Id

The Id of the status for this order.

CartId String True

The Id of the cart from which this order is assigned.

Status String True

Order Statuses.

TotalTax String True

The total value of the order.

SubtotalTax String True

The value for subtotal tax.

SubtotalExTax Decimal False

The value for subtotal, excluding tax.

SubtotalIncTax Decimal False

The value for subtotal, including tax.

ShippingCostExTax Decimal False

The value of shipping cost, excluding tax.

ShippingCostIncTax Decimal False

The value of shipping cost, including tax.

HandlingCostExTax Decimal False

The value of handling cost, excluding tax.

HandlingCostIncTax Decimal False

The value of handling cost, including tax.

IpAddress String False

IPv4 Address of the customer, if known.

WrappingCostExTax Decimal False

The value of wrapping cost, excluding tax.

WrappingCostIncTax Decimal False

The value of wrapping cost, including tax.

TotalExTax Decimal False

The total value of the order, excluding tax.

TotalIncTax Decimal False

The total value of the order, including tax.

HandlingCostTax String True

The value of handling cost.

HandlingCostTaxClassId Integer True

The Id of handling cost.

ShippingCostTax String True

The value of shipping cost.

ItemsTotal Integer False

The total number of items in the order.

ItemsShipped Integer False

The total number of items that have been shipped.

PaymentMethod String False

The payment method for this order.

PaymentProviderId String False

The external Transaction ID/Payment ID within this order's payment provider (if a payment provider was used).

PaymentStatus String True

The payment status for this order.

RefundedAmount Decimal False

The amount refunded from this transaction.

GiftCertificateAmount String True

Gift Certificate Amount.

CurrencyId String True

Currencies. Id

The ID of the currency being used in this transaction.

CurrencyCode String True

The currency code of the display currency.

CurrencyExchangeRate String True

The exchange rate between the default currency and display currency of store.

DefaultCurrencyId String True

Currencies.Id

The ID of the default currency for the store.

DefaultCurrencyCode String False

The currency code of the transactional currency the shopper pays in.

StaffNotes String False

Any additional notes for staff.

CustomerMessage String False

Message that the customer entered.

DiscountAmt Decimal False

Amount of discount for this transaction.

EbayOrderId String False

Ebay order number if order is placed through Ebay.

GeoIpCountryIso2 String False

The country where the customer made the purchase, in ISO2 format.

ShippingAddressCount Integer True

The number of shipping addresses associated with this transaction.

CouponDiscount String True

Discount of the coupon.

OrderSource String True

Source of the order.

IsDeleted Boolean True

Indicates whether the order was deleted (archived).

OrderIsDigital Boolean False

Indicates whether this is an order for digital products.

ExternalSource String False

A value identifying the system used to generate the order (for orders submitted or modified via the API).

ExternalId String False

ID of the order in another system.

ExternalMerchantId String False

Id of the external merchant.

ChannelId String False

Shows where the order originated.

TaxProviderId String False

BasicTaxProvider - Tax is set to manual; AvaTaxProvider - This is for when the tax provider has been set to automatic and the order was NOT created by the API; (blank) - When the tax provider is unknown.

ProductsUrl String False

Url of the products.

ProductsResource String False

Resource of the products.

BillingFirstName String False

Addressee first name.

BillingLastName String False

Addressee last name.

BillingCompany String False

Addressee company.

BillingStreet1 String False

Street address (first line).

BillingStreet2 String False

Street address (second line).

BillingCity String False

Addressee city

BillingZip String False

ZIP or postal code

BillingCountry String False

Addressee's country

BillingCountryIso2 String False

Addressee's country code

BillingState String False

The name of the state or province. Should be spelled out in full, for example, California.

BillingEmail String False

Recipient's email address.

BillingPhone String False

Recipient's telephone number.

BaseHandlingCost String False

The value of the base handling cost.

BaseShippingCost String False

The value of the base shipping cost.

BaseWrappingCost String False

The value of the base wrapping cost.

Pseudo-Columns

Pseudo column fields are used to enable the user to INSERT Fields that are non-readable but required during creation of new records.

Name Type Description
LinkedProducts String

Column for the aggregate table name holding order products.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060