Ready to get started?

Download a free trial of the Avalara Data Provider to get started:

 Download Now

Learn more:

Avalara AvaTax Icon Avalara ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with AvaTax.

Automate Avalara AvaTax Integration Tasks from PowerShell



Are you in search of a quick and easy way to access Avalara AvaTax data from PowerShell? This article demonstrates how to utilize the Avalara AvaTax Cmdlets and the CData ADO.NET Provider for Avalara AvaTax for tasks like connecting to Avalara AvaTax data, automating operations, downloading data, and more.

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 Driver?

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 AvaTax. To access Avalara AvaTax data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Avalara AvaTax.

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 AvaTax\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 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 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 System.Data.CData.AvalaraAvatax.AvalaraAvataxParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()