Aligned Solutions Consulting

Epicor Insights 2017: DMT PowerShell Session

Rick
06/03/2017 12:36 AM Comment(s)
I presented two sessions at Epicor Insights 2017:

Tuesday: DMT PowerShell Scripts, Lessons Learned

Thursday: Epicor Search Tools.
Here is the content of the DMT PowerShell session, including the PowerPoint in PDF format and the example PowerShell scripts presented during the session. Please leave a comment if you download the PowerPoint, scripts or found this topic helpful or how you plan to use DMT PowerShell to seed other's imagination.

 If you could ever use any Epicor consulting services, be sure to check out how I can help and let me know, I'd love to help! I would also like to thank Kathie Poindexter for planning and scheduling the session and Gary Parfrey for providing expert DMT Support during the session.

What You Need

You will need a few things to implement DMT PowerShell solutions, so first make sure you have everything you need:


I found that the last one was the most difficult to obtain.  My hope is to provide several examples to help you find problem/opportunities to use DMT PowerShell as the solution.

 How It Works

Everything here is just standard PowerShell.  DMT provides the necessary interface that can be called from a command prompt, batch script, PowerShell and reportedly even VB.  With these commands the following functions can be performed and are demonstrated in the sample scripts:

  1. DMT Load
  2. DMT BAQ Extract
  3. DMT SQL Extrat
  4. Results Logging
  5. Emailing

While the samples provide much of the syntax needed, there are many arguments that can be used.  You can obtain the full list of DMT arguments by entering 'DMT.exe /?' at a command line or running 'Start-Process DMT.exe /?' from the PowerShell prompt.  Depending on what you need you can construct the script different ways.

Examples

Here are a few examples of how DMT can be used:
  • Scripted Data Migration: If you are sure your source data files are good you can write a PowerShell script that calls each Add or Update load sequentially in the correct order for an unattended migration process once adequate testing confirms the data and process is solid.
  • SQL Data Sync: If you have direct access to another SQL database you can use DMT PowerShell scripts to execute a select query to a file and then load that file as Add or Update into Epicor.
  • Other Source Data Sync: Have a data source that support SOAP or REST API's?  With some skills you can use PowerShell to call those Web Services to collect the response, format it into a CSV that can then be loaded into Epicor via DMT.
Here are some examples that I have implemented for clients:
  • Automated Quantity Adjustments:  Each night clear out a specific inventory bin with an appropriate reason code.
  • Bug Workaround: For some unknown reason some Customer Ship Pack header fields (Invoiced & Status) does not update when invoiced.  Each night a BAQ provides the ShipHead records that are out of sync with the ShipDtl records and a DMT load corrects these ShipHead records.
  • Lock Qty on Printed Jobs: Planning doesn't want Job change suggestions on any Jobs that have had their Job Traveler printed.  Nightly BAQ results of any Jobs that are printed but the Lock Qty is not enabled. Using DMT to enable the Lock Qty field prevents MRP from making Qty change suggestions.
  • Update an Order Header UD Status Field: Need a user-friendly status for orders.  Hourly BAQ that contains a calculated field that determines the new status and then use DMT Update to set the new UD field status.
  • Log File Monitor: While there are many ways to do this, I used PowerShell to monitor the MultiCompany Log last modify date and if it has not been changed after a few minutes a warning email is sent to the administrator.

Struggles

In addition to being a PowerShell Novice, there are a few key issues I struggled with:
  • Syntax for multiword arguments.  The very first DMT Load I needed to script was a two word load name 'Quantity Adjustment.'  I first thought there may have been some shorthand aliases that DMT included, like QtyAdj or something, but nothing worked.  I then figured I needed to use the actual load name and put quotes or double quotes around it, but that errored out as well.  I tried many escape characters I was familiar with such as blackslashes, but nothing worked.  Finally I found that the PowerShell escape character is the 'backtick' AKA, 'grave accent.'  This is usually the key to the left of the 1 key.
  • DMT Epicor User Scope. When running the DMT process, one of the arguments that must be provided is the Epicor username & password.  The DMT process will have the very same Epicor module scope as the Epicor user, so it is important to make sure that user has access to the Company, Plant and menu items you would run the DMT loads.  It is also important to note that there is not any arguments for the DMT process to specify the Company & Plant as it will just use the last Company & Plant that the Epicor User accessed.  For this reason is it very important to always include the Company & Plant ID's whenever possible.  When the DMT Process is running a load it can process across multiple companies & plants in the same load, provided that the company & plant ID's are provided, otherwise the load will run against the last Company & Plant.
  • PowerShell Running Process Scope.  Keep in mind that PowerShell is just calling the DMT process and the PowerShell script itself will not time out.  When a PowerShell DMT load fails, the DMT process will continue to run, even if the Task Scheduler is used to start the PowerShell script and is configured to cancel after a timeout period.  This is because the Scheduled Task is the PowerShell script NOT the DMT process which the PowerShell script called.  While I have not attempted it, adding some error handling or a timeout loop to kill the DMT process may be possible.

Setup

As a System Administrator setting up things in an organized and obvious way is critical to having a manageable system for myself and future administrators.  Most of the time there are academic best practices that can be used; however when working with software like Epicor I try to understand the standard ways that the Epicor developers tried to set things up and follow that pattern.

  1. Install DMT on your Epicor App Server. If you intended to have the PowerShell script setup on a Scheduled Task, then it needs to be installed on a system that will be online at all times.  In most cases installing DMT on your Epicor App Server will do the trick.
  2. Create a DMT folder in the EpicorData folder.  You will need a folder location to store both the DMT Load csv files and the resulting DMT log files.  Using the Epicor EpicorData folder seemed to be a logical place for storing these files.
  3. Create shared BAQ's that generate DMT Load csv files. Often times the data that needs to be added, updated or deleted is already in Epicor but just needs some transformation.  Use of calculated fields to perform the transformation is critical.
  4. Construct The PowerShell Script.  The PowerShell script will called the DMT process with arguments that instruct DMT what you want to do, the available modes are Export & Import (Add, Update & Delete).  When using Export, a BAQ ID will need to be defined.  The Export will generate a csv file in the location specified in the path argument.  Then the Import mode can be used to reference that file for Add, Update or Delete into Epicor.

PowerShell Syntax

In addition to the attached PowerShell Syntax Samples, here is a few examples broken down.  Please note that all $ notations are variables that would need to be defined in your script:

Extracting Data: SQL. PowerShell includes the Invoke-SqlCmd allowing you to execute SQL queries against a local SQL database, provided that the user account running the PowerShell script has access to the database. This will output the results of the SQL query in a CSV file.


#Import the PS SQL module Import-module "sqlps" -DisableNameChecking #Extract SQL Data Invoke-SqlCmd -Query "SELECT TOP 50 'EPIC06' As Company, ProductNumber As PartNum, Name As PartDescription, (Case when = 0 then 'P' else 'M' END) as TypeCode FROM .." | Export-Csv -NoType $Source

Extracting Data: Epicor BAQ. I prefer to use the BAQ Designer, I have found that I can build some very complex queries quicker with the BQA Designer. In Epicor 10 the BAQ Designer really seems to help construct queries quickly.  In this case the DMT Process is called and several arguments are passed:                                                                                                                                

#Extract BAQ Data Start-Process -Wait -FilePath $DMTPath -ArgumentList "-User $User -Pass $Pass -Export -BAQ CustomerDetails -Target $Source -NoUI -ConfigValue=ERP10"


  • -Wait - This is the PowerShell Start-Process argument that tells the script to not continue until the called process completes.  If you exclude this argument then the script will call the process and then move on to the next section of code.  Since we need to wait until the extraction is complete and a CSV file exists before attempting to load the -wait argument is needed.
  • -FilePath $DMTPath - this is the Start-Process path that is being started.  Here the variable $DMTPath is used as it is defined earlier in the script.
  • -ArgumentList - this is the Start-Process argument so that a string of arguments can be passed to the started process.  In this case the DMT process will need the Epicor userID, password, Mode, BAQ ID, csv file target location, and the app server configuration ID.  Most of these are variables set at the beginning of the script.
Loading Data: Now that the source data has been generated in a CSV file, it can be loaded with the following: 

#Load Data Start-Process -Wait -FilePath $DMTPath -ArgumentList "-User $User -Pass $Pass -Add -Update -Import Customer -Source $Source "

-Wait - Depending on how your data needs to be loaded, sequentially or consecutively you can either include or exclude this Start-Process argument.  By excluding this argument, the script can start the DMT process consecutively loading multiple files at the same time.  Be aware of your data interdependencies, usually waiting is best.

-FilePath $DMTPath - this is the Start-Process path that is being started.  Here the variable $DMTPath is used as it is defined earlier in the script.

-ArgumentList - this is the Start-Process argument so that a string of arguments can be passed to the started process.  In this case the DMT process will need the Epicor userID, password, Mode, Import Name, csv file source location, and the app server configuration ID.  Most of these are variables set at the beginning of the script.

Scheduling the PowerShell Script

After testing your PowerShell script thoroughly the last and most important step is to schedule your PowerShell script, if you want the script to be ran on a schedule.  This is done with the Windows Task Scheduler. Here is another reference for creating Scheduled Task for a PowerShell Script.

    1. Open Windows Task Scheduler on the system DMT is installed on the PowerShell script exists.
    2. Create a Basic Task via the Actions pane.
      1. Set the Trigger (time or server event), probably recurring.
      2. Configure the Action as "Start as program".
      3. Set the Program/script to "Powershell.exe".
      4. Define the arguments as "-File ".

    Depending on your scheduling needs, you may create a single PowerShell script with multiple DMT extracts, loads, etc. on one or more schedules or multiple scripts with one or more schedules.

Final Thoughts

Attached are several resources below to assist with getting your DMT PowerShell scripts up and running.  Please let me know if I can be of any service regarding design, creation, setup or anything else with DMT PowerShell scripts.  Please leave a comment if you download the PowerPoint, scripts or found this topic helpful or how you plan to use DMT PowerShell to seed other's imagination.
 Please leave a comment if you download the PowerPoint, scripts or found this topic helpful or how you plan to use DMT PowerShell to seed other's imagination.

Rick