Skip to main content

DSP® Toolkit Developer Guide

Jenny Drummey
  • Updated

Toolkit Overview

NOTE: This article applies to BackOffice Associates® Solutions versions 6.0 and later.

The Toolkit is a feature of Common that allows for custom development of import and export Excel files and for the file and folder operations copy, move, zip, delete and execute Data Services jobs.

Intended Audience

The audience of this guide is developers who are implementing toolkit features who have an advanced knowledge of DSP® navigation and functionality.

Support

Custom development using the toolkit is permitted; however, it is not covered by standard Product Support. Refer to a technical lead for customization troubleshooting. If you believe there is a bug in the feature as implemented in the delivered product, report the bug to Product Support using the standard Support notification process.

Excel Import and Export Adapters

The Excel Import / Export adapter component of the Toolkit allows for the real-time creation of Excel files on the application server as well as the import of Excel files to tables in a WebApp’s database. 

Export files generated by the Export adapter follow this format:

Row 1: Technical Column name

Row 2: Friendly column name put throughDSP® translation

Row 3 – n: Data

The Excel Import Adapter expects that Excel sheets uploaded for import match the above format.  When the import runs, the adapter matches Technical column names from the worksheet (Row 1) with columns names in the ExcelImportTableName and only imports data for columns where the column names match. This allows for columns to be added to the Excel worksheets for calculations, formulas, etc. without affecting the import.

Event Registration

In order to use the Excel Import/Export adapters, register the following plugins as business or validation rules on a page as necessary:

  • Import: RAD.Excel: ExcelImportAdapter
  • Export: RAD.Excel: ExcelExportAdapter

Columns Used as Parameters for the Plugin

The following columns are used to configure the execution on the Excel adapters:

Column Name

Description

Default

Valid Values

Example

Required

 

 

ExcelExportTableName

The name of the table/view in the calling WebApp’s database from which the Excel export will be generated.  Only required for Export.

BLANK

Text

webCustomerExcelExportSel

ExcelImportTableName

The name of the table in the calling WebApp’s database into which imports from Excel files will be processed.  Only required for Import.

BLANK

Text

Customer

Common

 

 

ExcelUseExcel2007

The Excel format to use in the generated file.

FALSE (Excel 97-2003 format)

1 or 0

1

ExcelWhereClause

A well-formed SQL where clause without the word WHERE.

 

Export:  If ExcelIncludeDataInExport is TRUE, the where clause to use when generating data to export

Import:  If ExcelDeleteOnImport is TRUE, the where clause to use when deleting data from the import table

EMPTY

Text

CustomerName = N’BOA’

Import

 

 

ExcelUploadFileName

The location of the file to use for import on the application server.  It is recommended that the full physical path to the file be used.  If a virtual path is used, the navigation of the virtual path begins at the [BOA Installation directory]\web\UserArea\[Calling WebApp’s WebAppID]\ folder

EMPTY

Text

\CustomerExport.xlsx

ExcelDeleteOnImport

If TRUE, data will be deleted from the ExcelImportTableName before data from the Excel file is imported

FALSE

1 or 0

1

ExcelImportSheetName

The name of the Worksheet in the Excel workbook that contains the data for import

“Data”

Text

CustomerData

ExcelImportDefaultColumn

Name of a column on the ExcelImportTableName to default to a value for each record imported

EMPTY

Text

CustomerID

ExcelImportDefaultValue

Value to write to the ExcelImportDefaultColumn for each record imported

EMPTY

Text

1546

ExcelImportBatchSize

Number of records to write to the database from the Excel file at one time during the import process

5000

int

2500

ExcelCreateTableForImportIfNotExists

If TRUE, a table will be created named for the ExcelImportTableName containing all of the data imported.  The table also contains an Identity column set as the primary key of the table

FALSE

1 or 0

0

ExcelImportDataSourceID

DataSourceID override for the Excel Import table name

EMPTY

CranSoftDataSourceID

as a string

“80A575C2-31A6-489A-970F-BB4DA239E2B6”

Export

 

 

ExcelExportFileName

The name and path (on the application server) of the Excel file to create

WebAppName_
ExcelExportTableName_
TimeStamp

Text

\CustomerExport.xlsx

ExcelIncludeDataInExport

If TRUE, the created Excel file will include data from the ExcelExportTableNAme.  If FALSE, the generated Excel file will only contain column names

FALSE

1 or 0

1

ExcelExportSheetName

The name of the Worksheet to create in the generated Excel file

“Data”

Text

CustomerData

ExcelExportFieldList

A comma-separated list of fields to include/exclude from the ExcelExportTableName when generating the export Excel file

EMPTY

Text

AddedBy, AddedOn

ExcelExportFieldListOption

A determination as to whether the ExcelExportFieldList includes a list of columns from the ExcelExportTableName to include in the export or a list of columns to exclude

Exclude

“Include”
or “Exclude”

Exclude

ExcelExportOrderBy

Order By clause to apply to exported data

EMPTY

Text

LastName

ExcelExportDataSourceID

DataSourceID override for the Excel Export table name

EMPTY

CranSoftDataSourceID

as a string

“80A575C2-31A6-489A-970F-BB4DA239E2B6”

ExcelExportCreateDirectory

Flag to create the output directory.  If true then the output directory will be created

FALSE

1 or 0

1

 

File and Folder Operations

The file and folder operations supported by the toolkit are:

  • Copy, move, zip and delete files
  • Copy, zip and delete folders
  • Execute Data Services jobs

Data Row Contracts

A Data Row Contract is the set of columns that are used as parameters for the plugin. Listed are the three data row contracts that are used in this suite, the required and optional fields, and which plugins they correspond to. Please note that related plugins use the same contract.

CopyMoveDataRowContract

  • Required – IOCopySource, IOCopyTarget, IOCopyMask
  • Optional – IOCopyMakeDirectory (True), IOCopyOverwrite (False), IOCopyRecursive (False)
  • Used By – CopyFiles, Copy Folder, MoveFile

DeleteDataRowContract

  • Required – IODeleteSource, IODeleteMask
  • Optional – IODeleteMinimumCreatedAge (default 0), IODeleteMinimumChangedAge (default 0),       IODeleteRecursive (False), IODeleteEmptyFolders (False)
  • Used By – DeleteFiles, DeleteFolder

ZipDataRowContract

  • Required – IOZipSource, IOZipTargetArchive, IOZipMask
  • Optional – IOZipOverwrite (False), IOZipMakeDirectory (True), IOZipRecursive (False),                                             IOZipDeleteAfterArchive (False)
  • Used By – ZipFile, ZipFolder

Plugins

Legend:  Values in () are defaults

Most of the plugins take file name masks in order to filter on filenames (including extensions). “*” and “?” are recognized as “zero or more characters” and “zero or one character,” respectively. For example, the mask *.xlsx would match any Excel 2007 file (the extension is “.xlsx”) and the mask *.xls? would match both Excel 2003 and Excel 2007 files (“.xls” and “.xlsx”).

CopyFiles

CopyFiles copies all files in a directory.

Parameters:

  • Required – IOCopySource, IOCopyTarget, IOCopyMask
  • Optional – IOCopyMakeDirectory (True), IOCopyOverwrite (False)

Description: Puts a copy of all files in the IOCopySource directory that conform to the IOCopyMask filter into the IOCopyTarget directory. It overwrites same-named files if IOCopyOverwrite is set. It makes any needed folders if IOCopyMakeDirectory is set (otherwise, it returns an error if a folder is missing).

CopyFolder

CopyFolder copies a folder and all subfolders to another folder.

Parameters:

  • Required – IOCopySource, IOCopyTarget, IOCopyMask
  • Optional –IOCopyMakeDirectory (True), IOCopyOverwrite (False), IOCopyRecursive (False)

Description: Puts the IOCopySource directory and all files into the IOCopyTarget folder. It does the same for subfolders if the IOCopyRecursive flag is set. It overwrites same-named files if IOCopyOverwrite is set. It makes any needed folders if IOCopyMakeDirectory is set (otherwise it returns an error if a folder is missing).

MoveFile

MoveFile renames a single file.

Parameters:

  • Required – IOCopySource, IOCopyTarget
  • Optional – IOCopyOverwrite (False)

Description: Renames the IOCopySource file to the IOCopyTarget. It overwrites the target file if the IOCopyOverwrite flag is set.

ZipFile

ZipFile archives a single file into a zip file.

Parameters:

  • Required – IOZipSource, IOZipTargetArchive
  • Optional – IOZipOverwrite (False), IOZipMakeDirectory (True), IOZipDeleteAfterArchive (False)

Description: Puts a copy of IOZipSource file into the IOZipTargetArchive. It overwrites IOTargetArchive if the IOZipOverwrite flag is set. It creates new folders if needed if IOZipMakeDirectory is set. It deletes the original file if the IOZipDeleteAfterArchive flag is set.

ZipFolder

ZipFolder archives a folder and all subfolders into a zip file.

Parameters:

  • Required – IOZipSource, IOZipTargetArchive, IOZipMask
  • Optional – IOZipOverwrite (False), IOZipMakeDirectory (True), IOZipRecursive (False), IOZipDeleteAfterArchive (False)

Description: Puts a copy of all of the files in the IOZipSource folder into the IOZipTargetArchive. It overwrites IOTargetArchive if the IOZipOverwrite flag is set. It creates new folders if needed if IOZipMakeDirectory is set. It recursively zips subfolders and files if IOZipRecursive is set. It deletes the original files if the IOZipDeleteAfterArchive flag is set.

DeleteFiles

DeleteFiles deletes all files in a directory according to a specified mask.

WARNING: This action cannot be undone.

Parameters:

  • Required – IODeleteSource, IODeleteMask
  • Optional – IODeleteMinimumCreatedAge (default 0), IODeleteMinimumChangedAge (default 0), IODeleteRecursive (False), IODeleteEmptyFolders (False)

Description: Deletes all files in the IODeleteSource folder that conforms to IODeleteMask if the Created On date is older than IODeleteMinimumCreatedAge days ago and the Modified On date is older than IODeleteMinimumChangedAge days ago. It deletes files from subdirectories if the IODeleteRecursive flag is set. It deletes empty folders after files are deleted if IODeleteEmptyFolders is set.

DeleteFolder

DeleteFolder deletes a folder and all contents within.

WARNING: This action cannot be undone. This can delete the contents of an entire disk if the root of the disk is specified.

Parameter:

  • Required – IODeleteSource

Description: Deletes the specified folder. Any contents inside are lost.

Job Executor

Job Executor allows users to execute a Data Services job given the Data Services connection and the job name.

NOTE: To perform these steps, a user must understand how to create a page view in the platform.

  1. Create a page view (Horizontal or Vertical) with an event. The view must have the two required columns in the table below to execute a Data Services job.

Column

Data Type

Description

Required

DataSourceID

Uniqueidentifier

Populated from the [DSPCommon].[dbo].[ttDataSourceRegistry] table. The DataSourceID must be of Data Source Type "Data Services Repository (%)". All fields on the General tab of the Data Source Registry page’s Vertical View must be populated.

JobName

String

The name of the job in Data Services to execute.

Optional

DataServicesJobExecutorID

 

Unique identifier

GUID that ties additional functionality to a particular execution, such as  the ability to add global variables, cancel the execution job and add logging for the execution status.

GlobalVariablesViewName

 

String

The name of the view that contains the Global Variables. This view must contain the following columns:

  • (uniqueidentifier) DataServicesJobExecutorID - Tied to the Execution Record
  • (String) Name - Name of the Global Variable
  • (String) Value - The value of the Global Variable

CancelViewName

String

The name of the view that allows you to cancel the job while it is running. The view must  contain the following columns:

  • (uniqueidentifier) DataServicesJobExecutorID - Tied to the Execution Record
  • (int) boaStatus - Cancel Status is 17.

LogTableOrViewName

(String)

The Table or View to write the Execution Results to. The View or Table MUST contain the following columns:

  • (uniqueidentifier) DataServicesJobExecutorID - Tied to the Execution Record
  • (int) RunID - The unique ID assigned by Data Services for the execution. This ID can be used to track down the job status in Data Services Designer
  • (String) Status - The status of the Job. ProcessingCompleted, ProcessingFailed, StatusReset etc.
  • (int) Duration - The time in milliseconds (ms) that the job took to execute.
  • Message - A message in regards to the status.

2. Register the applicable public plugin as an event to the page.

Description: Makes a call to execute a Data Services job given the Name and a registered Data Services instance. Has the ability to pass in Global Variables and log results to a table. If a Cancel view is passed in with boaStatus 17, it can potentially cancel the job while it is running.

NOTE: Long running Data Services jobs should be executed in the background.

Related articles