Load Data into NAV using C/AL and Powershell (SQL From NAV Part I)
Load Data into NAV using C/AL & Powershell
Sample Scenario
“Read distributed data from Sql Server and load a data warehouse using NAV C/AL with Powershell integration”
If you need to retrieve (and aggregate) data from more than ONE company or from more than ONE database , in NAV you can use “SQL Server Views” objects mapped as “NAV linked objects”.
… but if don’t want to use NAV Linked objects (SQL Tables or SQL View because is complex and time consuming to managing with many companies\many databases or because you don’t’ want to consume NAV tables for linked objects) and you don’t like “.net integrations” or utilization of external applications, you can use Powershell in NAV (and outside NAV certainly…)
You can achieve this goal using a Hybrid Methods to load data into NAV from different sources
- Read data from Powershell
- Write Data\Processing in NAV with C/AL Coding
You can use two functions by Waldo to do this:
- Powershell Wrapper for NAV C/AL function
- Invoke-NAVSQL function
Scenario
- Use good Waldo function to launch powershell fro NAV (after this you can schedule on NAV NAS load data process) – PowerShell Wrapper
- Read Sql Views from Powershell using Waldo ‘Invoke-NAVSQL’ function
- Write into NAV table using C/AL logic & data validations mixing C/AL and Powershell commands.
NB: Powershell consumes a concurrent user.
Details about these good functions by Waldo
“How-to” use powershell in NAV (NAV Powershell Wrapper)
- look at Waldo Blog to achieve this functionality http://www.waldo.be/2014/12/17/running-powershell-from-nav/
- Download module here http://mibuso.com/downloads/running-powershell-in-nav
- “It’s basically a wrapper to easily use “System.Management.Automation.Runspaces.Runspace” and “System.Management.Automation.PowerShell”.
“How- to” load data into NAV with Powershell? (Invoke-NAVSQL)
- You can use Waldo function “ Invoke-NAVSQL” (function is similar to existing function part of the SQLPS module)
- “Invoke-NAVSQL”: Execute SQL queries on NAV databases with PowerShell
- http://www.waldo.be/2016/06/08/invoke-navsql-execute-sql-queries-on-nav-databases-with-powershell/
- Download function here: https://github.com/waldo1001/Cloud.Ready.Software.PowerShell/blob/master/PSModules/Cloud.Ready.Software.NAV/SQL/Invoke-NAVSQL.ps1
Example
Get all companies, and get the customers of the first company:
$Mycompanies = Invoke-NAVSql -ServerInstance ‘dynamicsNAV90’ -SQLCommand ‘Select * From Company’
$Customers = $Mycompanies |
select -First 1 |
foreach{ Invoke-NAVSQL -ServerInstance ‘dynamicsNAV90’ -SQLCommand “select * from [$($_.Name)$(‘$Customer’)]” -ShowWriteHost }
$Customers | select ‘No_’, Name, ‘Credit Limit (LCY)’ | Format-Table –AutoSize
ALL Source links:
http://www.waldo.be/2014/12/17/running-powershell-from-nav/
http://mibuso.com/downloads/running-powershell-in-nav
http://www.waldo.be/2016/06/08/invoke-navsql-execute-sql-queries-on-nav-databases-with-powershell/