SSIS Components for TaxJar

Build 21.0.7930

Customers

Lists existing customers.

Select

The component will use the TaxJar API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the component.

  • CustomerID support the following operators: =, IN
The following queries are processed server side:
    SELECT * FROM Customers
    SELECT * FROM Customers WHERE CustomerID = '123'
    SELECT * FROM Customers WHERE CustomerID IN ('123', '1234')
You can turn off client-side execution by setting SupportEnhancedSQL to false in which case any search criteria that refers to other columns will cause an error.

Insert

To add a Order, at least the TransactionID, TransactionDate, ToCountry, ToZip, ToState, Amount, Shipping, SalesTax need to be specified. Also we can execute an insert in 2 ways.
Using an aggregate to specify the exempt regions:

  INSERT INTO Customers (CustomerID, ExemptionType, Name, Country, State, ZIP, City, Street, ExemptRegionsAggregate)
    VALUES ('123', 'wholesale', 'Dunder Mifflin Paper Company', 'US', 'PA', '18504', 'Scranton', '1725 Slough Avenue', 
      '[
          {
            "country": "US",
            "state": "FL"
          },
          {
            "country": "US",
            "state": "PA"
          }
      ]')
Or using temporary tables to specify the exempt regions:
INSERT INTO CustomerExemptRegions#TEMP(State, Country) VALUES ('PA', 'US')
INSERT INTO CustomerExemptRegions#TEMP(State, Country) VALUES ('FL', 'US')

INSERT INTO Customers(CustomerID, ExemptionType, Name, Country, State, ZIP, City, Street, ExemptRegionsAggregate)
VALUES ('123', 'wholesale', 'Dunder Mifflin Paper Company', 'US', 'PA', '18504', 'Scranton', '1725 Slough Avenue', 'CustomerExemptRegions#TEMP')

Update

Similarly to the Insert operation we can update an item in 2 ways.
Using an aggregate to specify the exempt regions:

UPDATE Customers SET CustomerID = '123', ExemptionType = 'non_exempt', Name = 'egi', Country = 'US', State = 'CA', ZIP = '90002', City = 'Los Angeles', Street = '123 Palm Grove Ln', ExemptRegionsAggregate = '[
      {
        "country": "US",
        "state": "FL"
      },
      {
        "country": "US",
        "state": "PA"
      }
    ]' WHERE CustomerID = 123
And using temporary tables to specify the exempt regions:
INSERT INTO CustomerExemptRegions#TEMP(State, Country) VALUES ('PA', 'US')
INSERT INTO CustomerExemptRegions#TEMP(State, Country) VALUES ('FL', 'US')
UPDATE Customers SET CustomerID = '1', ExemptionType = 'non_exempt', Name = 'egi', Country = 'US', State = 'CA', ZIP = '90002', City = 'Los Angeles', Street = '123 Palm Grove Ln', ExemptRegionsAggregate = 'CustomerExemptRegions#TEMP' WHERE CustomerID = 123

Delete

In order to delete a Customer the CustomerID needs to be specified, for ex.

DELETE FROM Customers WHERE CustomerID = '100'

Columns

Name Type ReadOnly Description
CustomerID [KEY] String False

Unique identifier of the given customer.

ExemptionType String False

Type of customer exemption: wholesale, government, other, or non_exempt.

Name String False

Name of the customer.

Country String False

Two-letter ISO country code of the customers primary address.

State String False

Two-letter ISO state code of the customers primary address.

ZIP String False

Postal code of the customers primary address.

City String False

City of the customers primary address.

Street String False

Street address of the customers primary address.

ExemptRegionsAggregate String False

Places where the customer is exempt.

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7930