Thursday, 1 September 2016

Custom Integration Services Data Provider

Hi All,

Today I want to share with you one way how you can extend Acumatica data export/import providers or add a new one to integrate with new system.

To accomplish this task you need some development skills as creation of new provider require some code writing. But you can write this code with any possible way:
  • Visual Studio
  • Acumatica Customization browser
  • Notepad or any compatible application

To create a new provider you need create new class that will implement a IPXSYProvider interface:
public interface IPXSYProvider
       string ProviderName { get; }
       string DefaultFileExtension { get; }
       PXSYParameter[] GetParameters();
       void SetParameters(PXSYParameter[] parameters);
       PXStringState[] GetParametersDefenition(); 
       string[] GetSchemaObjects();
       PXFieldState[] GetSchemaFields(string objectName);
       void Export(string objectName, PXSYTable table, bool breakOnError,                                    Action<SyProviderRowResult> callback);
       PXSYTable Import(string objectName, string[] fieldNames, PXSYFilterRow[] filters,                              string lastTimeStamp, PXSYSyncTypes syncType);

Lets describe these methods and properties
  • General properties
    • string ProviderName; - Just a name of provider, which will be visible on the user interface.
    • string DefaultFileExtension; - Default file extension that will be used for creating of new files.
  • Parameters it is a name value pair of configuration values that users can provide to you provider to adjust integration process.
    • PXSYParameter[] GetParameters();  - Acumatica can get parameters values at the runtime. 
    • void SetParameters(PXSYParameter[] parameters);  - Through this method Acumatica will push parameters from UI to provider.
    • PXStringState[] GetParametersDefenition(); - Through this method Acumatica will inform user that the provider expect some parameter to be run.
  • Schema - it is information about data sources, that should looks like multiple as a flat data- tables with columns. In general providers was designed to work with excel files.
    • string[] GetSchemaObjects(); - Need to return all objects that provider can use. For Excel provider it can be sheets, for SQL provider – tables.
    • PXFieldState[] GetSchemaFields(string objectName); - By each provided object name need to return all fields that can use this provider. For Excel and SQL provider it can be columns.
  • Export/Import Operations
    • void Export(string objectName, PXSYTable table, bool breakOnError, Action<SyProviderRowResult> callback); - Through this method Acumatica pushes all data to export.
      • objectName – provider object name
      • table – table with all data to export
      • breakOnError – user asked to break on first export error
      • callback – way to inform Acumatica that line export completed successfully or failed
    • PXSYTable Import(string objectName, string[] fieldNames, PXSYFilterRow[] filters, string lastTimeStamp, PXSYSyncTypes syncType); - Acumatica invokes this method to get data for import operation.
      • objectName – provider object name
      • fieldNames – specific list of fields for import operation
      • filters – set of filters to filter data on source
      • lastTimeStamp – time stamp of last import operation
      • syncType – specific type of import operation (Full, Incremental_AllRecords, Incremental_NewOnly)
Ok, now lets take a code of your provider, or you can use the example of provider that is provided below.
Code should be packed in DLL or just in *.cs file that you can put on the test Acumatica site. If you use DLL, than you should put it to <instance>\bin folder, or if you use *.cs file you can put in to <instance>\App_Code folder.
Actually that's all - you can refresh browser page and check the providers selector.

Based on the value of ProviderName property you can see the provide on the UI.

Several tips during provider development:
  • If you want to have incremental import you need to implement filtering of data on provider side. Acumatica base code will not do any filtering.
  • The same is above for Source Restrictions, which is some sort of provider side filtering. Acumatica assumes that filtering will be done on provider side.
  • You can use UploadFileMaintenace graph to get and save files to/from database from provider dynamically. Do not save file on file system, as it may rise a problem during multi-threading.
  • Use some base classes provided by Acumatica to reuse existing logic. You also can inherit exiting provider and change it's logic.

CSV Provider Code Snippet:

Some base classes that exists in Acumatica and can help you with implementing new provider:
  • PXSYBaseProvider – adds some help logic to work with parameters.
  • PXSYBaseSchemaProvider : PXSYBaseProvider – adds some help logic to work with schema.
  • PXSYBaseFileProvider : PXSYBaseSchemaProvider – adds possibility to work with files attached to Acumatica provider screen. 
  • PXSYBaseEncodedFileProvider : PXSYBaseFileProvider – adds some help logic to work with encoding. Helpful for writing plain text files.

Have a nice integration!