Use PowerShell and Business Connector to retrieve data from Dynamics AX (AX2009)

Not long ago I started a new position as an in-house AX specialist. Part of my job is to provide support to users on their AX issues. Here I face a challenge that, due to organization structure, I don't have direct access to the SQL database. If I want to quickly check some data in AX I am then left with the table browser. Another option is to write X++ jobs but then I'll have to take care of showing the data (e.g. using infolog) myself. In short, it's not ideal.

I missed the ability to write ad-hoc scripts and the ability to conveniently re-run them (Used to be T-SQL scripts for me). Then I thought I can use PowerShell! I tried to look up the internet for PS scripts to access AX and saw this. Unfortunately, I couldn't get it to work. Well, time to write my own. =D

Here I connect to AX using the business connector. First I have to get the Microsoft.Dynamics.BusinessConnectorNet.dll and put it somewhere. Load it up and create an Axapta object from it.

   
$targetPath = "C:\Temp\Microsoft.Dynamics.BusinessConnectorNet.dll"

[reflection.Assembly]::Loadfile($targetPath) | Out-Null
$ax = new-object Microsoft.Dynamics.BusinessConnectorNet.Axapta          

Then I connect to AX and retrieve data.

   
$company = ""
$language = ""
$aos = ""
$config = ""
$ax.logon($company,$language,$aos,$config)
             
$t1 = $ax.CreateAxaptaRecord("SalesTable")
$t2 = $ax.CreateAxaptaRecord("SalesLine")
$ax.ExecuteStmt("SELECT * FROM %1 JOIN %2 WHERE %1.SalesId == %2.SalesId && %1.SalesId == 'SO-100004'",$t1,$t2)  
Do 
{ 
    $t1.get_field("salesId") + " - " + `
    $t2.get_field("itemId") + "; " + `
    $t2.get_Field("SalesQty") + "; " + `
    $t2.get_Field("LineAmount")
} 
while ($t1.Next())

The scripts above returns below when run against the demo database.
SO-100004 - 1151; 2; 121.38
SO-100004 - 1151; 2; 124.42
SO-100004 - 1161; 5; 197.95
SO-100004 - 1161; 7; 333.83
SO-100004 - 1401; 1; 204.39
SO-100004 - 1402; 1; 257.53
SO-100004 - 1606; 2; 800

What's written above works but it just run a very specific query. I'll share a basic script module I put together in my next post. It'll have better reusability.

This posting is provided "AS IS" with no warranties, and confers no rights.