Automate Avalara AvaTax Integration Tasks from PowerShell

The CData Cmdlets for Avalara AvaTax are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Avalara AvaTax.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Avalara AvaTax, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Avalara AvaTax data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Avalara. To access Avalara AvaTax data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Avalara.

Once you have acquired the necessary connection properties, accessing Avalara AvaTax data in PowerShell can be enabled in three steps.

The primary method for performing basic authentication is to provide your login credentials, as follows:

• User: Set this to your username.
• Password: Set this to your password.

Optionally, if you are making use of a sandbox environment, set the following:

• UseSandbox: Set this to true if you are authenticating with a sandbox account.

Authenticating Using Account Number and License Key

Alternatively, you can authenticate using your account number and license key. Connect to data using the following:

• AccountId: Set this to your Account Id. The Account Id is listed in the upper right hand corner of the admin console.
• LicenseKey: Set this to your Avalara Avatax license key. You can generate a license key by logging into Avalara Avatax as an account administrator and navigating to Settings -> Reset License Key.

PowerShell

1. Install the module:

   Install-Module AvalaraAvataxCmdlets

2. Connect:

   $avalaraavatax = Connect-AvalaraAvatax -User "$User" -Password "$Password"
   

3. Search for and retrieve data:

   $code = "051349"
   $transactions = Select-AvalaraAvatax -Connection $avalaraavatax -Table "Transactions" -Where "Code = `'$Code`'"
   $transactions
   

   You can also use the Invoke-AvalaraAvatax cmdlet to execute SQL commands:

   $transactions = Invoke-AvalaraAvatax -Connection $avalaraavatax -Query 'SELECT * FROM Transactions WHERE Code = @Code' -Params @{'@Code'='051349'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Avalara\lib\System.Data.CData.AvalaraAvatax.dll")
    

2. Connect to Avalara AvaTax:

    
   $conn= New-Object System.Data.CData.AvalaraAvatax.AvalaraAvataxConnection("User=MyUser;Password=MyPassword;")
   $conn.Open()
   

3. Instantiate the AvalaraAvataxDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT Id, TotalTax from Transactions"
   
   $da= New-Object System.Data.CData.AvalaraAvatax.AvalaraAvataxDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.totaltax
   }
    

Update Avalara AvaTax Data

PowerShell

Update-AvalaraAvatax -Connection $AvalaraAvatax -Columns @('Id','TotalTax') -Values @('MyId', 'MyTotalTax') -Table Transactions -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.AvalaraAvatax.AvalaraAvataxCommand("UPDATE Transactions SET Code='051349' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.AvalaraAvatax.AvalaraAvataxParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Avalara AvaTax Data

PowerShell

Add-AvalaraAvatax -Connection $AvalaraAvatax -Table Transactions -Columns @("Id", "TotalTax") -Values @("MyId", "MyTotalTax") 

ADO.NET

$cmd = New-Object System.Data.CData.AvalaraAvatax.AvalaraAvataxCommand("INSERT INTO Transactions (Code) VALUES (@myCode)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.AvalaraAvatax.AvalaraAvataxParameter("@myCode","051349")))
$cmd.ExecuteNonQuery()

Delete Avalara AvaTax Data

PowerShell

Remove-AvalaraAvatax -Connection $AvalaraAvatax -Table "Transactions" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.AvalaraAvatax.AvalaraAvataxCommand("DELETE FROM Transactions WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.AvalaraAvatax.AvalaraAvataxParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject