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.
Acumatica data export/import

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.
    Acumatica data export/import
    • 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.
    Acumatica data export/import
    • 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!