Load Data into NAV using C/AL and Powershell (SQL From NAV Part I)

Load Data into NAV using C/AL & Powershell

powershell cAL 

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)

“How- to” load data into NAV with Powershell? (Invoke-NAVSQL)

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/

https://github.com/waldo1001/Cloud.Ready.Software.PowerShell/blob/master/PSModules/Cloud.Ready.Software.NAV/SQL/Invoke-NAVSQL.ps1

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.